Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone!
This is my first post on PBI forum!
I am working on a problem where we are trying to identify the UPCs (products) which have taken a price increase over a period of 4 weeks by comparing against the mean price of each UPC for 6 weeks. I have attached the PBIX file for reference. The data in the file has columns UPC and Week and some fact columns (Value, Volume, Price)
Link to PBIX file: https://drive.google.com/file/d/1pgaMe3JpPNVdoZYwJtIDRyoTlrQg8d_e/view?usp=sharing
Sample of the data looks like this (full data in the PBIX file shared):
UPC | Week | Value Sales | Volume Sales | Avg_Price |
1111 | 1 | 6128 | 4002 | 1.531234383 |
1111 | 2 | 4003 | 9462 | 0.423060664 |
2222 | 1 | 2702 | 964 | 2.802904564 |
2222 | 2 | 2760 | 5294 | 0.521344919 |
Requirement:
Count of distinct UPCs where each of the latest 4 weeks Price is greater than Mean Price of 6 weeks.
This needs to be measure driven to be scalable. Users would want to select a particular period and the calculation needs to be dynamic based on the selection.
My Process:
Cont_Growth =
var Mean_Price = filter(addcolumns(SUMMARIZE(Dummy_Data,Dummy_Data[UPC],Dummy_Data[Week]), "Price",divide(CALCULATE(SUM(Dummy_Data[Value Sales])), CALCULATE(SUM(Dummy_Data[Volume Sales]))),"Mean", CALCULATE(average(Dummy_Data[Avg_Price]),ALLSELECTED(Dummy_Data[Week]),Dummy_Data[Week] <7)),Dummy_Data[Week]<5)
var min_Price = MINX(Mean_Price,[Price] - ([Mean]))
var final = FILTER(ADDCOLUMNS(summarize(Dummy_Data,Dummy_Data[UPC]),"Cond_check2", min_Price),[Cond_check2] > -.45)
return COUNTAX(final,Dummy_Data[UPC])
Problem:
When all the UPCs are satisfying the condition, I get the correct count. Else, I am still getting the correct count within UPC context. However, as a total I am getting a blank(). Please see image below. Here I have given condition of Price - Mean > -.45 just to check the below scenario.
Any help would be greatly appreciated! Pretty stuck on this problem! 🙂
Thanks & Regards,
Shamik
Solved! Go to Solution.
Hi @shamikdas
I guess I know what you mean
Cont_Growth 1 =
VAR T1=GENERATE(GROUPBY(Dummy_Data,Dummy_Data[UPC],Dummy_Data[Week]),
VAR Price1=divide(CALCULATE(SUM(Dummy_Data[Value Sales])), CALCULATE(SUM(Dummy_Data[Volume Sales])))
VAR Mean1=CALCULATE(average(Dummy_Data[Avg_Price]),ALLSELECTED(Dummy_Data[Week]),Dummy_Data[Week] <7)
VAR check=IF((Price1-Mean1)>-.45,1,0)
RETURN
ROW("CHECK",check))
VAR final =
GROUPBY( FILTER( T1,Dummy_Data[Week]<5),[UPC],
"total",COUNTX(CURRENTGROUP(),[UPC]),
"cont",SUMX(CURRENTGROUP(),[CHECK]))
RETURN
SUMX(final,IF([cont]/[total]=1,1,0))
Hi, @shamikdas
Have you followed the DAX formula posted by Vera_33 to find the solution to your problem?
If so, would you like to mark his reply as a solution so that others can learn from it too?
Thanks in advance!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @Vera_33
To your question below:
I am not sure what you want in Total as Distinct count products, in your sample it should be 3 or 8? I am taking 8
I would want the output to show 1 here. The reason is that out of the 3 products (1111, 2222, 3333) only 1 product satisfies the condition for all 4 time periods. If you see above example, the products 2222 and 3333 satisfy the condition in 3 out of 4 weeks and 1 out of 4 weeks respectively. Hence I would like to ignore these products and only count the 1st one.
Is there some way I could achieve this? Appreciate the quick support! 🙂
Thanks!
Shamik
Hi @shamikdas
I guess I know what you mean
Cont_Growth 1 =
VAR T1=GENERATE(GROUPBY(Dummy_Data,Dummy_Data[UPC],Dummy_Data[Week]),
VAR Price1=divide(CALCULATE(SUM(Dummy_Data[Value Sales])), CALCULATE(SUM(Dummy_Data[Volume Sales])))
VAR Mean1=CALCULATE(average(Dummy_Data[Avg_Price]),ALLSELECTED(Dummy_Data[Week]),Dummy_Data[Week] <7)
VAR check=IF((Price1-Mean1)>-.45,1,0)
RETURN
ROW("CHECK",check))
VAR final =
GROUPBY( FILTER( T1,Dummy_Data[Week]<5),[UPC],
"total",COUNTX(CURRENTGROUP(),[UPC]),
"cont",SUMX(CURRENTGROUP(),[CHECK]))
RETURN
SUMX(final,IF([cont]/[total]=1,1,0))
Hi @Vera_33
Thank you so much for the solution! Works perfectly! 🙂
I will now try to modify this for my real data which is in star schema format (though I dont expect any issues in that)! I will also try to see if I can pull out the sales for the UPCs satisfying my condition!
Regards,
Shamik
Hi @shamikdas
I am not sure what you want in Total as Distinct count products, in your sample it should be 3 or 8? I am taking 8
Sligtly modified yours
Cont_Growth 1 =
var Mean_Price = filter(addcolumns(SUMMARIZE(Dummy_Data,Dummy_Data[UPC],Dummy_Data[Week]),
"Price",divide(CALCULATE(SUM(Dummy_Data[Value Sales])), CALCULATE(SUM(Dummy_Data[Volume Sales]))),
"Mean", CALCULATE(average(Dummy_Data[Avg_Price]),ALLSELECTED(Dummy_Data[Week]),Dummy_Data[Week] <7)),
Dummy_Data[Week]<5)
var final =FILTER( ADDCOLUMNS(Mean_Price,"check",[Price]-[Mean]),[check]>-.45)
return
COUNTROWS(final)
another way, the same result
Cont_Growth 2 =
VAR T1=GENERATE(GROUPBY(Dummy_Data,Dummy_Data[UPC],Dummy_Data[Week]),
VAR Price1=divide(CALCULATE(SUM(Dummy_Data[Value Sales])), CALCULATE(SUM(Dummy_Data[Volume Sales])))
VAR Mean1=CALCULATE(average(Dummy_Data[Avg_Price]),ALLSELECTED(Dummy_Data[Week]),Dummy_Data[Week] <7)
VAR check=Price1-Mean1
RETURN
ROW("CHECK",check))
VAR final =FILTER( T1,[CHECK]>-.45&&Dummy_Data[Week]<5)
RETURN
COUNTROWS(final)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |