Forum Discussion
Community ChampionStdDev from Mean by Category
Thanks for the suggestion v-haibl-msft. I actually solved this over the weekend a different way but I'll see if I can find the time to go back and try that method. What I ended up doing was merging my three tables into a single table and then wrapping the calculation of the average in a CALCULATE statement with an ALLEXCEPT clause that included the two categories that I wanted to factor into the calculation dynamically. Worked as expected once the data was all in a single table. You can actually see the end result here in the Data Stories Gallery:
I just published it last night. Thanks again KGrice for taking a look at this also.
Memorable MemberHi Greg_Deckler. I think this works, but I'm not sure if you're wanting to show your rural and not rural scores in the same measure depending on where the shool is, or if they should be separate. Here's what I have now based on the sample data:
And here are the measures in the order they appear:
Overall Score District Mean = AVERAGE(DISTRICT[OVERALL SCORE])
Overall Score District StdDev = STDEV.P(DISTRICT[OVERALL SCORE])
Mean All Districts = CALCULATE(
[Overall Score District Mean],
ALL(District[DISTRICT NAME])
)
StdDev All Districts = CALCULATE(
[Overall Score District StdDev],
ALL(District[DISTRICT NAME])
)
Overall Score StdDevs from Mean =
([Overall Score District Mean] - [Mean All Districts]) / [StdDev All Districts]
Mean All Districts Rural = CALCULATE(
[Mean All Districts],
Topologies[ Major Grouping]="Rural"
)
StdDev All Districts Rural = CALCULATE(
[StdDev All Districts],
Topologies[ Major Grouping]="Rural"
)
Overall Score StdDevs from Mean Rural = IF(
HASONEVALUE(Topologies[ Major Grouping]),
IF(
VALUES(Topologies[ Major Grouping])="Rural",
([Overall Score District Mean] - [Mean All Districts Rural]) / [StdDev All Districts Rural],
BLANK()
),
([Overall Score District Mean Rural] - [Mean All Districts Rural]) / [StdDev All Districts Rural]
)