Forum Discussion

klehar's avatar
klehar
Helper V
2 years ago
Solved

Color Code matrix based on average value of Geos

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 :

GeoKPIValues
North America% completition90
India% completition78
Japan% completition82
China% completition56
Europe% completition89
North America% TAT40
India% TAT78
Japan% TAT92
China% TAT94
Europe% TAT86

 

My desired output : 

 

I was referring to another community post

https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting-of-Matrix-based-on-Total-average/m-p/822050

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

 

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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:

     

8 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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's avatar
      klehar
      Helper V
      2 years ago

      _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's avatar
      klehar
      Helper V
      2 years ago

      _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's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        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.