Hierarchical conditional formatting for values and totals on per row basis in matrix

[vandna_R]

HI Team,

I am trying to do conditional formatting in my matrix on row hierarchy.

the formatting is such each row has its own scale based on min and max value in that trow.

I am getting the correct output but when i drill down to next level , all the values of above level looses its colour.

Can someone please help me with my problem.

I want for values and total both .

I tried to do formatting for values and total .. but it is asking for custom min and max values which i dont have.. each row is checking its min and max value and accordingly providing the colour scale.

ex:

country       march 1   march2   march3

+Abc            2                 54          12

+XYZ           0                  50          1032

here for row 1: value 2 will have the light colur and 54 will have the darkest shade of the colour

for row 2: 0 will have the lightest and 1032 will have the darkest irrspective of the above row.

but when i do drill 

country   march 1   march2   march3

     State

  Abc            2                 54          12

     Pqr          0              10             8

     QWE        2               44            4

+XYZ           0                  50          1032

now states appear in colour but all the other country looses their colour(ex: XYZ).

I need colour formatting for both the levels simultaneously.

Please help me.

[amitchandak]

@vandna_R , Create a color measure like this. You have to create a few or create a few logics using isinscope or isfiltered

Use this measure in conditional formatting after using field option

Measure example

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

[vandna_R]

Hi,

Thank you for your inputs i have checked oth the provided links

but my problem still persists 

the issue is i am selecting date range in slicer the measure is not taking the highest value in that range and give it darkest colour.

rahter it is taking max value from the complete row as i have created calculated columns for that :

Max Level 2 value = CALCULATE(MAX([value]),FILTER([level 2 column=EARLIER([level 2 column])))

and the same formula for level 1 using summarized table ignoring level 2 column then used a measure for assigning colours to them based on the highest value.

from here i am getting max values for both but in the complete row..

I want to get max value for a row in selected date range (dates are in column)

Please suggest me something.