Color Code matrix based on average value of Geos

[klehar]

Hi,

I want to color code the measures data such that if a cell value > Average than show Green

if it is less than Average than show as Red

Data :

Geo	KPI	Values
North America	% completition	90
India	% completition	78
Japan	% completition	82
China	% completition	56
Europe	% completition	89
North America	% TAT	40
India	% TAT	78
Japan	% TAT	92
China	% TAT	94
Europe	% TAT	86

My desired output : 

I was referring to another community post

https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting-of-Matrix-based-on-Total-av...

but that doesnt seem to work, neither did that DAX make sense.

My request to the one who replies : Please explain the DAX as well

[_AAndrade · Accepted solution]

Hi @klehar,

Here is my solution:
My table:

I added the "Order" column to sort the Geo as you have in your desired output.
In this table the Geo column is sorted by the olumn Order as you can see here:

Measures I used:

Value Each Geo = MIN(T_DataGeo[Values])  -- To return each value that I have in the table. You need to adjust this measure according to your needs

Avg KPY = 
CALCULATE(
    AVERAGE(T_DataGeo[Values]),
    ALLSELECTED(T_DataGeo[Geo], T_DataGeo[Order])
)
-- This measure returns the avegage to each KPY 

Color Measure = 
IF(
    [Value Each Geo] >= [Avg KPY],
    "GREEN",
    "RED"
)
-- Return the colour according to the rules you mentioned

On the Cell elements apply a Background color for the measure "Value Each Geo":

Final Output:

[klehar]

@_AAndrade Can this be also done when my column "KPI" is actually a measure and not a column

In my real data % completion looks like this : 
% completition = 
VAR numerator = calculate(...)

VAR denominator = calculate(...)

RETURN divide(numerator, denominator, 0)

[_AAndrade]

Hi,

Yes it's possible to do it, by using that measure instead of my "Valeu each Geo" measure and computes the avg measure to compare one against other.
For the new AVG measure probably you will need to use summarize function, but it depends on your model.  

[klehar]

@_AAndrade can this also be done when the second column I mentioned (called KPI) is actually a measure and not a column? So you cant see it the way I show it in the table above

[klehar]

@_AAndrade cna you please attach the pbix as well

[_AAndrade]

@klehar,
I'm attaching the pbix file.

[klehar]

@_AAndrade what was the reason to create the Geo order column?

[_AAndrade]

As I said before I added this column to order Geo Country like you have in your tables.

Starts from North America to Europe otherwise we will have the standard alphabetic sort.

You can ignore them if you don't need that particular sort