Color Coding Matrix based on Average Total Value

[klehar]

Hi,

I have 2 tables

Dimension Geo which has 3 values

Geo

North America

India

China

Fact Escalation : which has a measure called % Esclalation measure

Geo	Opp ID	Type
North America	op1	Escalated
North America	op2	Normal
North America	op3	Normal
North America	op4	Normal
North America	op5	Normal
India	op6	Escalated
India	op7	Normal
India	op8	Escalated
India	op9	Normal
India	op10	Normal
China	op11	Normal
China	op12	Normal
China	op13	Escalated
China	op14	Escalated
China	op15	Escalated

% esclation = 
var nr = calaculate(distinctcount(opp id), type = "Escalated")
var dr = calculate(distinctcount(oppid))

RETURN divide(nr,dr,0)

All I want is a matrix like this with color coding 

If value of geo < average then red else green.

[v-yiruan-msft · Accepted solution]

Hi @klehar ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below to get the average value

Average = AVERAGEX(ALLSELECTED('Geo'[Geo]),[% esclation]) 

2. Create a measure as below

Conditional formatting = IF ( ROUND ( [% esclation], 2 ) < ROUND ( [Average], 2 ), "red", "green" )

3. Apply the conditional formatting on the matrix

Best Regards

[klehar]

@_AAndrade