Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a table (product) of product category with Quantity,year,month and monthID inside it (monthID is equal to (year-2006)*12+monthnumber).
For example
Product Qty Year month
A 5 2020 1
B 2 2020 3
I would calculate the standard deviation of the product category on last 12 months (some products can have qty 0 in certian month)
So I create a measure Total_Case_YTD:=SUM(Product[Quantity]) and
STDEV Qty over Last 12 Months:=STDEVX.P (
FILTER (
Product;
Product[MonthID] <= MAX ( Product[MonthID])
&& Product[MonthID]
>= MAX (Product[MonthID] ) - 11
);
Total_Case_YTD+0
)
But when I pivot them (row with product, column standard deviation) all standard deviation are equal to zero
I.e
Product STD deviation
A 0
B 0
Do you have any suggestion?
Thanks
Antonio
Solved! Go to Solution.
Hi, @Ortignano
Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.
Tab:
Calendar(a calculated table):
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"YM",
YEAR([Date])*100+MONTH([Date])
)
There is no relationship between two tables. You may create measures as below.
Sum Qty over last 12 Months =
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
SUM(Tab[Qty]),
FILTER(
ALLEXCEPT(Tab,Tab[Product]),
Tab[YearMonth] in
TOPN(
12,
CALCULATETABLE(
DISTINCT('Calendar'[YM]),
FILTER(
ALL('Calendar'),
[YM]<=ym
)
),
[YM]
)
)
)
STDEV Qty over last 12 Months =
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
STDEV.P(Tab[Qty]),
FILTER(
ALLEXCEPT(Tab,Tab[Product]),
Tab[YearMonth] in
TOPN(
12,
CALCULATETABLE(
DISTINCT('Calendar'[YM]),
FILTER(
ALL('Calendar'),
[YM]<=ym
)
),
[YM]
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Ortignano
Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.
Tab:
Calendar(a calculated table):
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"YM",
YEAR([Date])*100+MONTH([Date])
)
There is no relationship between two tables. You may create measures as below.
Sum Qty over last 12 Months =
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
SUM(Tab[Qty]),
FILTER(
ALLEXCEPT(Tab,Tab[Product]),
Tab[YearMonth] in
TOPN(
12,
CALCULATETABLE(
DISTINCT('Calendar'[YM]),
FILTER(
ALL('Calendar'),
[YM]<=ym
)
),
[YM]
)
)
)
STDEV Qty over last 12 Months =
var ym = MAX(Tab[YearMonth])
return
CALCULATE(
STDEV.P(Tab[Qty]),
FILTER(
ALLEXCEPT(Tab,Tab[Product]),
Tab[YearMonth] in
TOPN(
12,
CALCULATETABLE(
DISTINCT('Calendar'[YM]),
FILTER(
ALL('Calendar'),
[YM]<=ym
)
),
[YM]
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Calculate (stdev.p(values(product), calculate (sum(quantity)), datesinperiod(date,-12,month))
Something like this..
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Please provide (a sample of) you data