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