Forum Discussion

Troops's avatar
Troops
Frequent Visitor
6 years ago

DAX For standard deviation of a calculated difference

Hi

 

I have a dataset ('all') which has date, conversion rate of a website and a couple of dimensions to split the data by e.g. device.

 

I also have a "day on day" measure which is simply compares conversion rate for each day to the prior day and calculates the difference.

 

I want to now create another measure which takes all the "day on day" values and calculates the standard deviation.

 

The ultimate aim is to add a metric that says if the DoD variance is outside of a typical daily fluctuations (e.g. 2 standard deviations based on the previous n days) then flag it as a 1.

 

Using the STDEV.P('all',[day_on_day]) seems to work at a total level, but as soon as I bring in one of the dimensions it no longer works and returns 0.

I've tried to create a table 

 

Table = SUMMARIZE('All'
, 'All'[date]
, 'All'[device]
, "Conv_rate", [Conv_rate]
, "DoD", [day_on_day] )

 

and then calculating a standard deviation within that table based on a date range but this doesn't work either.

 

How can I resolve this and have a measure that says, for this date and device, what the standard deviation is the the day on day measure based on the previous n days for that same device?

1 Reply

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support

    Hi Troops,

    If possible, could you please inform me more detailed information (such as yor data sample, your current design and your expected output)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.