Forum Discussion

CLeDane's avatar
CLeDane
Frequent Visitor
6 years ago
Solved

Std. Dev. for a Monthly SUM

I need a measure that will calculate the std. deviation for a monthly sum. For instance, I have several costs posting to the same account in the first four months of this year. I would like to take the total monthly amount for each account and calulate a standard deviation of those 4 month totals.

 

Please see below screenshot (I cannot find where to attach .pbix file)

  • Anonymous's avatar
    Anonymous
    6 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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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

    • CLeDane's avatar
      CLeDane
      Frequent Visitor
      6 years ago

      This worked perfectly! I just couldn't get that formula's reference logic down.

       

      Thank you!