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
Anonymous
Not applicable

Dax to calculate average percentage at aggregated level

Hi,

Is there a way to aggregate a measure without summing? So here is my calculation for average % of volunteers. Right now, its showing the right calculation on the date level which is correct.

 

Average % per  Date = DIVIDE(SUMX(Site, Site[Internal Community] + Site[Internal Parent or Guardian Enrolled] + Site[Internal Site Staff] + Site[External]),7,0)*100
 

Here is what the data looks like in the table at the date level.

 
 
 

 

I'd like the average % volunteers measure not to sum up by city and site but give an aggregate percentage by site and city.I just feel like there's a DAX i'm missing here.

 

Here's what it looks like when it sums the data at site and city level. Definitely not what i want. Please help.

 

pbi 2.pngpbi 3.png

 

 

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Not very Clear.

But if want to change aggregation after a level, you can try a formula like

 

sumx(values(Site[Site]),[Average % per Date])

or

sumx(summarize(Site,Site[Site],Site[City],"_1",[Average % per Date]),[_1])

Anonymous
Not applicable

@amitchandak , I tried that but i'm getting the same values as my original dax. i think the dax i need is the summarizecolumn dax but i'm not sure how to use it within the dax i'm currently using. I took a stab at it but it giving me an error.

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

I figured it out. Here is the syntax i used and it worked perfectly.

 

SWITCH(TRUE(),
ISFILTERED(Schools[City]),[Average % per Date],
ISFILTERED(Schools[SchoolName]),[Average % per Date],
AVERAGEX(SiteSurvey_20_21,[Average % per Date])
) /100

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