Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to apply background color on a Matrix that contains a column "Distribution %" :
I want to perform a calculation and set the bg color ro red. The Calcuation is :
% > (TMo Target + Over Varaince) OR
% < (TMo Target + Under Variance),
Set "Red"
For this I wrote a measure (trying different ways):
Measure =
/*
SWITCH(
TRUE(),
//([% of MB by Total MB all PMN] > SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Over Variance Numeric]) ), "Red"
([% of MB by Total MB all PMN] > SUMX(OutboundVolumes, OutboundVolumes[OverTargetValue] + 0 ) ), "Green",
([% of MB by Total MB all PMN] < SUMX(OutboundVolumes, OutboundVolumes[UnderTargetValue] + 0) ), "Red"
//([% of MB by Total MB all PMN] < SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Under Variance Numeric]) ), "Red"
)
*/
VAR distriPerc = VALUE('OutboundVolumes'[% of MB by Total MB all PMN])
RETURN
IF ( distriPerc > (SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Over Variance Numeric]) ) ||
distriPerc < (SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Under Variance Numeric]) ),
"Red" )
/*
IF ([% of MB by Total MB all PMN] > SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Over Variance Numeric] ),
"Red",
IF ([% of MB by Total MB all PMN] < SUMX(OutboundVolumes, OutboundVolumes[Standard Target Display] + OutboundVolumes[Standard Under Variance Numeric]),
"Red", "Pink")
)
*/
//||
// ,))))
With the above results,
% Target + Over Target + Under
28 (5 + 10 = 15) ( 5 + -10 = -5) 28 > 15 - yet, it is not turned Red.
72 (95 + 5 = 100) (95 + -1 = 85) 72 < 85 - Turns Red
The Target + Over variance calc somehow doesn't work.
The data for above results looks like as below (all fields are of type %)
Values of Over variance field are:
I need help to figure out why the Target + Over calc is not working. Any help is appreciated.
Thank You
@Anonymous , Try a measure like this and use that in conditional formatting using field value option
If( [Distribution %] >[Tmo Target] + [Over Variance] , "Red", "White")
Thanks for your response @amitchandak
I wrote a measure as you stated:
OnlyOver_Measure = IF ([% of MB by Total MB all PMN] > SUMX(OutboundVolumes, [Standard Target Display] + [Standard Over Variance Numeric]) , "Red", "Green" )
And the result is Distribution % is shown as Green rather than Red.
The Distribution % & TMo Target cols in the Matrix are Measures returning % & Text values respectively. The Over Variance & Under Variance cols contains values of fields of type Text - the original data.
To work on the calcuations, I have duplicated the fields & made of type %.
To debug the above issue, I added those created % fields in Matrix, and I see different values than what it is in the data, they are shown as Sum & not just value.
Could this be the issue ??? But then, how come the under Variance % col is working perfectly well !!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |