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]
)
- Greg_Deckler
Thanks KGrice. This definitely works and essentially what I asked for, but not what I want! :)
I was *hoping* that I could make this measure dynamic such that I could put a slicer on the page and when the user clicked the slicer, it calculated things correctly.
The end go is that I will probably have two categories, "rural", "urban", etc. and then a category on level of poverty "<= 10%", "11%-20%".
Sorry, I feel like I'm whining... :)
v-haibl-msftMicrosoft Employee
Maybe you can try to use the ALLSELECTED function to make the measure dynamic according to your slicer.
Best Regards,
Herbert
- Greg_Deckler
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.
Hi KGrice,
I have almost the same problem as Greg but I've had to calculate my StdDev via the below using Summarize and Calculatetable. My data is a tranactional table, standard deviation is required at the monthly level with filtering to two seperate hierarchies built on location. I need the standard deviation (based on month) to dynmaically reflect the filters.
VAR _table = SUMMARIZE(CALCULATETABLE(Fct_Incident,Tbl_Calendar[CurMonthOffset]>=-26,Tbl_Calendar[CurMonthOffset] < -1), Fct_Incident[Start of Month],"_CountID",distinctcount(Fct_Incident[Incident ID]))VAR _STDEV = STDEVX.P(_table,[_CountID])My problem is the same as Greg's in that I want to Chart the results as a Statistical Process Chart (where I can control the "baseline" - this is problem I'm having with the availble add in apps) but I can't get the StdDev to display on each (month) row. Unfortunately the same solution that you provided for Greg doesn't work for me because I'm using a calculated/summarized table.Do you have any thoughts on how to do this? Or should I be using a different way to group by Fct_Incident[Start of Month].Would appreciate any assistance.
