Matrix Conditional Formatting (with Hierarchy)

[Anonymous]

Hi there,

I've created a matrix below that shows frequency of visit by animal (Rows) and clinic (Columns). I would like to conditional format the individual frequencies based on whether they are above or below the overall frequency.

For example, overall cat visits is 4.5. So Clinic C should be in green because 5.0>4.5. Clinics A & B should be in red because it is lower than 4.5

I calculated frequency using this created measure: 

Freq per day = Divide(CALCULATE(COUNTROWS(Sheet1)),CALCULATE(DISTINCTCOUNT(Sheet1[Day]),ALL(Sheet1[Type],Sheet1[Color])),0)

The data source looks like this (apologies I was unable to attach my excel & pbix file):

Day	Clinic	Type	Color
1	A	Dog	Brown
1	B	Dog	Brown
1	B	Dog	Brown
2	A	Dog	White
2	B	Dog	White
2	C	Cat	Grey
3	B	Cat	Grey
3	C	Cat	Grey
4	A	Cat	Black
4	A	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black
4	C	Cat	Black

Thank you in advance.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create 3 measures as below:

_total = DIVIDE(CALCULATE(COUNTROWS('Sheet1'),FILTER(ALL(Sheet1),'Sheet1'[Type]=MAX('Sheet1'[Type]))),CALCULATE(DISTINCTCOUNT(Sheet1[Day]),ALL(Sheet1)))

Measure = 
var _num=CALCULATE(COUNTROWS('Sheet1'),FILTER(ALL(Sheet1),'Sheet1'[Type]=MAX('Sheet1'[Type])&&'Sheet1'[Clinic]=MAX('Sheet1'[Clinic])))+0
var _deno=CALCULATE(DISTINCTCOUNT(Sheet1[Day]),FILTER(ALL(Sheet1),'Sheet1'[Type]=MAX('Sheet1'[Type])))
Return
IF(ISINSCOPE(Sheet1[Clinic]),DIVIDE(_num,_deno),BLANK())

Measure 2 = IF(ISBLANK('Sheet1'[Measure]),BLANK(), IF('Sheet1'[Measure]<[_total],"#FF3349","#151314"))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , at type level

M1  = Divide(CALCULATE(COUNTROWS(Sheet1)),CALCULATE(DISTINCTCOUNT(Sheet1[Day]),ALLexpect(Sheet1 , Sheet1[Type])),0)

at type color level

 M2 = Divide(CALCULATE(COUNTROWS(Sheet1)),CALCULATE(DISTINCTCOUNT(Sheet1[Day]),ALLexpect(Sheet1 , Sheet1[Type],Sheet1[Color])),0)

if You need to create a color measure if want both the 1 and 2 formula I suggested for color, then you need to use isinscope

like

Switch (true(),

isinscope(Table[Tyep])  && not(isinscope (Table[Color])) && [frequency per day] > [M1]  , "red" ,

// add other condition

"green"

)

Use this in conditional formatting using field value option