cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
td_beginner
Helper I
Helper I

Measure for Conditional Formatting works 50%

 

Hello, 

 

    I am trying to apply background color on a Matrix that contains a column "Distribution %" :

td_beginner_0-1631675488666.png

 

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 %)

td_beginner_1-1631676096235.png   

Values of Over variance field are: 

td_beginner_2-1631676268918.png

 

I need help to figure out why the Target + Over calc is not working. Any help is appreciated.

 

Thank You

 

 

 

2 REPLIES 2
amitchandak
Super User IV
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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

 

td_beginner_0-1631743016711.png

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 !!

 

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

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.