Forum Discussion
Std. Dev. for a Monthly SUM
- Anonymous6 years ago
Here is the measure... however, you should be aware that it does take into consideration all filtering of the Calendar table. So, for instance, if in each cell of a visual you'll have a single month but a slicer will be set to only show, say, data for weekends for the months, the measure will honor this as well, that is, it'll calculate the SD among the months but only for data visible in the months, not full months.
[SD] = var __sd = STDEVX.S( VALUES( 'Calendar'[Year-Month] ), [Total Amount] ) return // __sd could in certain circumstances // return NaN, so we need IFERROR to // account for such events IFERROR( __sd, 0 )Bear in mind that for this to work you have to have a Calendar in the model (a proper Date table). Here's how you should handle time-intel in PBI:
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
Best
D
Here is the measure... however, you should be aware that it does take into consideration all filtering of the Calendar table. So, for instance, if in each cell of a visual you'll have a single month but a slicer will be set to only show, say, data for weekends for the months, the measure will honor this as well, that is, it'll calculate the SD among the months but only for data visible in the months, not full months.
[SD] =
var __sd =
STDEVX.S(
VALUES( 'Calendar'[Year-Month] ),
[Total Amount]
)
return
// __sd could in certain circumstances
// return NaN, so we need IFERROR to
// account for such events
IFERROR( __sd, 0 )
Bear in mind that for this to work you have to have a Calendar in the model (a proper Date table). Here's how you should handle time-intel in PBI:
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
Best
D
This worked perfectly! I just couldn't get that formula's reference logic down.
Thank you!