Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a Sales table that is on an Order/Product level. This is the essential information:
Receipt_Date | Order_id | SKU_Code | Region | Country | Channel | Units |
01/01/2023 | XYZ | 123 | Asia | Japan | Retail | 3 |
01/01/2023 | XYZ | 878 | Asia | Japan | Retail | 1 |
01/01/2023 | ABC | 123 | Europe | UK | Digital | 2 |
02/01/2023 | PQP | 420 | Europe | Germany | Retail | 5 |
As you can see:
- Each row of my table represents a product present in one order. So if a transaction (XYZ, on my example above) had more than 1 product, there will be one row for each product.
- If there was more than one unit of a product being sold on the transaction, it will be populated on the Units column (like orders XYZ, ABC and PQP).
On PowerBI, I am trying to sum the units for the last 7 days (which I had no trouble doing), and I want to get a standard deviation of units sold in the past 7 days (current day + 6 days prior to current day).
With that information, I will use different visualisation tools - line charts, tables, etc.
My problem with using STDEV.P(Units) is that it will calculate the standard deviation on a row level. And I need to do that on a daily level + considering the last 7 days as the population.
I also need to allow my users to filter this information by region, channel, country, etc, so I cannot work with daily aggregations.
Any ideas?
Whilst it didn't completely solved my challenge, your post did spark some inspiration that helped me getting there:
You can use STDEVX.P for this, iterating over dates.
I'm going to assume you have a Date table (marked as a Date table) created with relationship to Sales[Receipt_Date].
Also say you have a measure
Units Sum =
SUM ( Sales[Units] )
Then create a measure like this:
Units Standard Deviation (7 days) =
CALCULATE (
STDEVX.P (
VALUES ( 'Date'[Date] ),
[Units Sum]
),
DATESINPERIOD ( 'Date'[Date], TODAY (), -7, DAY )
)
A shortened version that would also work:
Units Standard Deviation (7 days) v2 =
STDEVX.P (
DATESINPERIOD ( 'Date'[Date], TODAY (), -7, DAY ),
[Units Sum]
)
Does this method work for you?
Regards
User | Count |
---|---|
66 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |