Help to calculate a measure on a measure?

[Amanda_Allen]

Hello Everyone

I am hoping you might be able to help as I am relatively new to Power BI and I am stuck on how to do the following in the package.  I can easily perform what I need in Excel but I am avoiding the temptation as my company is transitioning from excel to Power BI and I am trying to learn good habits.

I have a measure called CALC_AVAIL_PCT_2 which is average availability of an asset filtered by dates.  In order to create the report I need I need to be able to calcuate the standard deviation of the values contained in the CALC_AVAIL_PCT_2 column, which are displayed by week_index (as this term allows me to filter back in time x amount of weeks).  If I create a measure using STDEV.S as shown in the table it is taking the standard deviation of the entire sample which is not what the report is wanting to show, only the standard deviation of the weekly averages (if that makes sense) not the individual row's.

Current measure calcs are:

CALC_AVAIL_PCT_STDEV_2 = CALCULATE(STDEV.S(Dumpers_Total[Availability_pct]),DATESBETWEEN('Calendar'[Date],MIN('Dates 5'[Week_Start]),MAX('Dates 5'[Week_End])))

CALC_AVAIL_PCT_2 = CALCULATE(AVERAGE(Dumpers_Total[Availability_pct]),DATESBETWEEN('Calendar'[Date],MIN('Dates 5'[Week_Start]),MAX('Dates 5'[Week_End]))) 

The blurred column is the asset identifier which I cannot show on a forum.

Cheers

Amanda

 

[Anonymous]

HI @Amanda_Allen,

I'm not so clear for your table structure and relationship, can you please share more detail informations?

How to Get Your Question Answered Quickly

For nested measures, you can take a look at below link:

Optimizing DAX expressions involving multiple measures 

BTW, you can also try to use following functions of standard deviations which support to add condition or filters on parameter table

STDEV.P Function (DAX)

STDEV.S Function (DAX)

STDEVX.P Function (DAX)

STDEVX.S Function (DAX)

Notice: please do mask on sensitive data before sharing.

Regards,

Xiaoxin Sheng