topic Re: How to calculate an average which will change based on slicer selection in DAX Commands and Tips

How to calculate an average which will change based on slicer selection

helen_p — Thu, 20 Aug 2020 21:32:14 GMT

Dear all

I am fairly new to power BI and am figuring it out as I go and was wondering if someone could offer some guidance...

I have a created a table similar to below

Week Commencing Count Of Additions Care Group Clinician

01/01/2020 3 A 1

07/01/2020 6 A 1

14/01/2020 4 A 1

01/01/2020 9 B 2

07/01/2020 8 B 2

14/01/2020 7 B 2

01/01/2020 9 A 3

07/01/2020 8 A 3

14/01/2020 7 A 3

I created a measure in my table so that I can calculate the average

CALCULATE(

AVERAGEX('waiting list_Aggregated',[Count Of WL Additions]),

ALLSELECTED('waiting list_Aggregated'))

I then applied 2 slicers to my report one for Care Group and another for clinician

My average measure only works at the most granular level (Clinician) and does not seem to calculate correctly for the Care Group column, which is the level of hierachy above clinician

I started to read about filtering and ratio to parent posts, but could not find a solution which will help me calculate an average which will calculate at different levels of hierachy

Could someone kindly advise and put me on the right track?

Thanks

Helen

Re: How to calculate an average which will change based on slicer selection

Greg_Deckler — Thu, 20 Aug 2020 22:46:19 GMT

helen_p - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

So, that being said, I think you could simplify your formula to either:

CALCULATE(

AVERAGE('waiting list_Aggregated'[Count Of WL Additions]),

ALLSELECTED('waiting list_Aggregated'))

AVERAGEX(ALLSELECTED('waiting list_Aggregated'),[Count Of WL Additions])

Re: How to calculate an average which will change based on slicer selection

amitchandak — Fri, 21 Aug 2020 02:22:37 GMT

helen_p , I do not see Count Of WL Additions in you table you shared.

Do you need an average of the count. Then it need to be

CALCULATE(
AVERAGEX(values('waiting list_Aggregated'[Week Commencing]),[Count Of WL Additions]),
ALLSELECTED('waiting list_Aggregated'))

allselected is needed when you need grand total, else this will work

CALCULATE(
AVERAGEX('waiting list_Aggregated',[Count Of WL Additions]),
)

Can you sample output in table format?