get the standard deviation of another measure

[Anonymous]

Hi 

I'm a beginner on power bi.  I am trying to calculate the average and the standard deviation for a group of durations.  I have created measures that calculate on other measures and I am not sure how they work.  The intention is that the measures can be filtered using other fields from the main table.  I would be grateful for any feedback. 

The first measure calculates the sum of a duration of a status across a number of rows.

Status1 = CALCULATE(SUM(StatusDurationTable[StatusDuration]),StatusDurationTable[Status]="Status1")

The second measure calculates the sum of 3 statuses into one status category:

StatusCategory= [Status1]+[Status2]+[Status3]

Then rather than create measures for each status to calcualte the average and then sum it I have created the following Measure:

Test Average =

VAR __table = SUMMARIZE(StatusDurationTable,[StatusDuration],"_Average",[StatusCategory])

RETURN AVERAGE(StatusDurationTable[StatusDuration])

And then I have created a measure to calculate the Standard Deviation

Test StdDev =

VAR __table = SUMMARIZE(StatusDurationTable,[StatusDuration],"_StandardDev",[Test Average])

RETURN STDEVX.P(__table,[Test Average])

The intention is that the standard deviation will refer to the the average duration of the status category when an additional filter is applied.  For example per project or category.  However I'm operating beyond the limit of my maths and power bi knowledge and would be grateful if someone would be able to confirm if any additional filters applied would cascade through the measures as expected.

Many thanks in anticipation.

[kentyler]

DAX measures execute inside the current filter context on the report. If you have a measure in a visual that is displaying rows by category, then the measure will only operate on the rows for that category.

This is why sometimes you need to use the ALL() function inside of CALCULATE(), to restore a greater set of the rows than are visible to the measure in the filter context when it is called.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return the VARs to check on your steps along the way.