Standard Deviation calculations returning Wrong Numbers

[Troops]

Hi

I have some simple data which contains date, visits and bookings. From this I calculate conversion rate (bookings/visits) and then from this I measure a day on day and week on week conversion rate metrics.

I now also want to calculate, for each day the standard deviation of the "day on day" and "week on week" metrics for the prior 100 days. This will help identify whether the DoD/WoW value for the current day is within a normal range but the calculation simply isn't working.

What's also weird is that it's is giving me the same (wrong) numbers for both standard deviation calculations so I've no idea what it's actually doing.

I have attached a link for the Power Bi and an excel which has the same data in and shows the values I'm expecting for one particular date (1st October). The values in Power Bi aren't even close for that same date.

https://www.dropbox.com/s/olg57wb32cqxfbu/Test.pbix?dl=0   - Power Bi file

https://www.dropbox.com/s/74sni6en7fw24a8/Sample%20Data.xlsx?dl=0 - Excel file

What am I doing wrong and can anyone provide a formula that will match the values in the excel file?

[v-eachen-msft]

Hi @Troops ,

Your two links are all xlsx files. I think you want the value displayed in the Power BI. You can download my test file and refer to it.

[Troops]

Hi

Thanks for the response. I was able to adapt the formulas used here for my report. Essentially mine wasn't working (I think) due to my DoD/WoW being custom metrics rather than custom columns.

Now I need to adapt further I think. In my actual data I have other columns e.g. device so that each date will now have 2 rows and the user can use a filter to pick device.

Will this logic still work? I'm thinking some of the custom columns you've created wouldn't work due to the index (i.e multiple rows with the same index).