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

[helen_p]

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

[amitchandak]

@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?

[Greg_Deckler]

@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'))

Or

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