cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amanda_Allen Visitor
Visitor

Help to calculate a measure on a measure?

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

 

 10-07-2018 12-10-17 PM.png

1 REPLY 1
Community Support Team
Community Support Team

Re: Help to calculate a measure on a measure?

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |