Dax to calculate average percentage at aggregated level

[Anonymous]

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.

[amitchandak]

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

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

[amitchandak]

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

[Anonymous]

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