Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Solved! Go to 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:
Proud to be a Super User!
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:
Proud to be a Super User!
@_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)
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.
Proud to be a Super User!
@_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,
I'm attaching the pbix file.
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |