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

[shamikdas]

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

[v-robertq-msft]

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.

[shamikdas]

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

[Vera_33 · Accepted 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))

[shamikdas]

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

[Vera_33]

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)