cancel
Showing results for
Did you mean:  Helper I

## Measure for Conditional Formatting works 50%

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

2 REPLIES 2  Super User IV

@td_beginner , Try a measure like this and use that in conditional formatting using field value option

If( [Distribution %] >[Tmo Target] + [Over Variance] , "Red", "White")

Proud to be a Super User!  Helper I

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 !! Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals. Top Solution Authors
Top Kudoed Authors
Users online (3,066)