topic Compare each week vs Mean value and distinct count products satisfying condition in DAX Commands and Tips

Compare each week vs Mean value and distinct count products satisfying condition

shamikdas — Sun, 21 Nov 2021 13:23:11 GMT

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.

Calculate Mean of Price for 6 weeks (this number will change later and the range of weeks will later be dynamic)
Compare each of the 1st 4 weeks with the mean value
If all the weeks are greater than mean, count the SKU, else ignore.
Finally show total count of SKUs where this condition is satisfied.

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:

Summarize data at UPC and week level and create columns with ADDCOLUMNS function à Price and Mean Price
Filter out with function FILTER the weeks I do not need to compare (e.g. weeks 5 to 8 in this data)
Further calculate the minimum of Price – Mean at UPC level
Count UPCs where the minimum is greater than 0 (or any threshold)
Below is the measure.

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

Re: Compare each week vs Mean value and distinct count products satisfying condition

Anonymous — Tue, 23 Nov 2021 12:29:01 GMT

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)

Re: Compare each week vs Mean value and distinct count products satisfying condition

shamikdas — Wed, 24 Nov 2021 04:20:07 GMT

Thanks Anonymous

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

Re: Compare each week vs Mean value and distinct count products satisfying condition

Anonymous — Wed, 24 Nov 2021 05:21:11 GMT

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))

Re: Compare each week vs Mean value and distinct count products satisfying condition

v-robertq-msft — Thu, 25 Nov 2021 03:09:44 GMT

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.

Re: Compare each week vs Mean value and distinct count products satisfying condition

shamikdas — Thu, 25 Nov 2021 07:40:56 GMT

Hi Anonymous

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