Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago
Solved

Percentage Difference Between column total column row value

I have a table similar to the below...

 

COUNTRY    SIMS

Value1          10

Value2          17

Value3         16

Value4         16

Value5         18

 

I then have a measure that gives me the avg of all the SIMs rows, so for the example above, the avg value would be 15.4. I then want to conditional format the SIMs column that if a value is +10% or -10% of the avg value (15.4), its font colour is changed to highlight is possible issue.

 

I have created a measure to give me the avg of the SIMs column...

 

Avg_Total_SIMs_Measure = DIVIDE(SUM('*** Output'[No. of SIM's]),COUNTROWS('*** Output'))
 
but unsure how to proceed.
Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    Hi StuartSmith 

     

    You can create the measure to find the overall average:

    [Overall Avg] =
    CALCULATE ( AVERAGE ( 'Table'[SIMS] ), REMOVEFILTERS ( 'Table'[COUNTRY] ) )

     

    Then another measure to flag it:

    [Overall Avg Within 10% Flag] =
    IF (
        AVERAGE ( 'Table'[SIMS] ) >= [Overall Avg] * 0.9
            && AVERAGE ( 'Table'[SIMS] ) <= [Overall Avg] * 1.1,
        1,
        0
    )

     

    Then you would use the flag measure with conditional formatting on the table to change the font color when flagged. Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

    Hope that helps!

2 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    5 years ago

    Hi StuartSmith 

     

    You can create the measure to find the overall average:

    [Overall Avg] =
    CALCULATE ( AVERAGE ( 'Table'[SIMS] ), REMOVEFILTERS ( 'Table'[COUNTRY] ) )

     

    Then another measure to flag it:

    [Overall Avg Within 10% Flag] =
    IF (
        AVERAGE ( 'Table'[SIMS] ) >= [Overall Avg] * 0.9
            && AVERAGE ( 'Table'[SIMS] ) <= [Overall Avg] * 1.1,
        1,
        0
    )

     

    Then you would use the flag measure with conditional formatting on the table to change the font color when flagged. Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

    Hope that helps!

    • StuartSmith's avatar
      StuartSmith
      Power Participant
      5 years ago

      Hi Zoe, thanks for replying.  

       

      Was Looking at your code and trying to understand the below 


          AVERAGE ( 'Table'[SIMS] ) >= [Overall Avg] * 0.9
              && AVERAGE ( 'Table'[SIMS] ) <= [Overall Avg] * 1.1,

      Isnt the code getting the Avg of 'Table'[SIMS] and then comparing with the value from the Overall Avg measure value? if so, thats not what I am after.

       

      I dont know the correct function to use, but i think it needs to be something like...

       

           'Table'[SIMS]  >= [Overall Avg] * 0.9
              &&  'Table'[SIMS]  <= [Overall Avg] * 1.1

       

      so it compares the value of each SIMs row with the OverAll Avg measure value and if the 'Table'[SIMS] row value is + or - 10% of the avg value then conditional formating is applied.  

       

      Thanks in advance,