How to do Conditional Format referring to group average Part II

DAX DAX Calculations
1

Hi team,
I have added Net result in my report. What I would like to highlight was Cost Centre with the Net Result worse than group average not the group total.
In my case, Cost Centre 1002,1008,1004,1005, and 1003 should be highlighted.
But with the total, it is only highlighted for 1003.

Can you please guide me the correct DAX.
Many thanks.

Regards.ConditionalFormattingBasedOnSubtotal.xlsx (49.4 KB) Q_AverageNetResultbyGroupForConditionalFormating.pbix (50.3 KB)

2

Hi @ammu

Please use below DAX.

Formatting Check SWITCH NR = 
VAR AverageNR = CALCULATE( [Net Result] , ALLEXCEPT( Facility, Facility[RAC Grouping] , Facility[Metro/Regional] ) )

RETURN
SWITCH( TRUE() ,
   [Net Result] < AverageNR || [Net Result] < 0 && [Net Result] <> AverageNR, -1 
)

image
image1727Ă—928 48.2 KB

4

Hi @ammu

Please use the below DAX to work on your actual data.

Formatting Check SWITCH NR = 
VAR AverageNR = CALCULATE(AVERAGEX(Data, Data[Income] - Data[Expense]), ALLEXCEPT( Facility, Facility[RAC Grouping] , Facility[Metro/Regional] ) )

RETURN
SWITCH( TRUE() ,
   [Net Result] < AverageNR, -1 
)
1 Like
5

Hi MK3010,
I have withdrawn the previous post as I would like to make sure what I meant.
Please see attached Power BI report and updated Data figures.
for Metro Group 50 Beds to 70 Beds. Average for the group is -5846 so that (cost centre 1008) should not be highlighted. However it was highlighted in the PowerBI with the Provided DAX. Because it was not the average, instead it was the total.

image
image1046Ă—516 38 KB

Please see attached PBIX and excel data for the reference.ConditionalFormattingBasedOnSubtotal.xlsx (60.6 KB) Solution_AverageNetResultbyGroupForConditionalFormating.pbix (52.9 KB)
Thank you.

6

Hi @ammu

Hope the above updated DAX code is what you were looking. I have used with the new dataset and it is skipping cost center 1008. Below screen shot FYR.

image
image1193Ă—662 35.5 KB

7

many thanks, MK,
My actual net result was itself a measure. how can I use AVERAGEX with Measure.

image
image941Ă—130 5.78 KB

Or
image
image914Ă—137 6.33 KB

How can incorporate AVERAGEX with my measure.

thank you.

regards,
Aye

8

Hi @ammu,

I got few work to do sorry for the inconvenience.

Could you please create new post (for better clarity and avoid confusion with the title) so that other member in the forum may feedback you before I get back.

9

thanks, MK.
I will try again.

Regards,
Aye

10

Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!