Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
helen_p
Frequent Visitor

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

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

 

2 REPLIES 2
amitchandak
Super User
Super User

@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
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors