Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm having difficulty adding up the number of times a particular store location has zero sales over a 4-week period for a particular product.
See the example above, what I need is a matrix that shows the audience only two things:
1.) The product in the row dimensions
2.) The number of stores where the most recent 4 weeks have shown zero sales
That's it.
I have tried the following:
1.) Created a custom column where if the week is one of the most recent 4, then the value is 1, else, 0.
2.) Created a measure counting the number of stores where sales quantity = 0 AND the custom column value =1.
The result was that I got the count of stores that had zero sales during any of the 4 weeks. I need to show stores that have had zero sales for the entirety of the 4 weeks.
So - if Store A, over the last 4 weeks, had one sale 4 weeks ago, and zero sales thereafter, it should not count; If store B over the last 4 weeks sold nothing, it should count.
Please let me know if you have enough information to help. Thank you so much in advance!
Solved! Go to Solution.
Hi @jlankford
I have uploaded a suggested solution here:
I recommend you create dimensions for Week, Store & Product. These sorts of calculations are safer when you have separate dimensions rather than relying on values being present in the fact table. I'm anticipating that you could have cases where a store sold zero of a particular product and no row exists rather than having an explicit zero value.
The final measure for counting number of stores with zero sales is:
Number of Stores with Zero Sales (recent 4 weeks) = VAR MaxWeek = MAX ( Week[Week No] ) VAR WeekFilter = TREATAS ( GENERATESERIES ( Maxweek - 3, MaxWeek ), Week[Week No] )
// Convenient way of generating list of last 4 weeks
RETURN COUNTROWS ( FILTER ( Store, CALCULATE ( SUM ( DATA[sales] ), ALL ( Week ), WeekFilter ) = 0 ) )
Regards,
Owen
Hi @jlankford
Sharing doesn't seem to be enabled for the link you sent through - could you turn on sharing?
The basic approach I would suggest is to create a measure that iterates over a Customer dimension and for each customer filters to those with zero sales in the last 4 weeks (by applying a date filter).
Hi again,
I'm either not skilled enough to do this right, or the method you suggested is not giving me a correct answer.
Something like:
LAST 4 WEEK SUM = CALCULATE(SUM(Master[POS Sales Dollars]), FILTER(Weeks,DATESBETWEEN(Weeks[WM Week,MAX(Weeks[WM Week]-4,MAX(Week[WM Week]))))
This accurately gives me the sum of the recent 4 weeks.
Then, i will do something like:
COUNT ZERO WEEKS = CALCULATE(COUNT(Master[Store NBR]),[LAST 4 WEEK SUM] = 0)
This will only give me all the times when the entire product has zero sales over the past 4 weeks across every store. So:
If a product even sells one unit out of 4800 stores, then it won't count it
If a product is discontinued and for some reason still in my dataset, it's the only one counted.
Does anyone have any ideas on what I might be doing wrong?
Hi @jlankford
I have uploaded a suggested solution here:
I recommend you create dimensions for Week, Store & Product. These sorts of calculations are safer when you have separate dimensions rather than relying on values being present in the fact table. I'm anticipating that you could have cases where a store sold zero of a particular product and no row exists rather than having an explicit zero value.
The final measure for counting number of stores with zero sales is:
Number of Stores with Zero Sales (recent 4 weeks) = VAR MaxWeek = MAX ( Week[Week No] ) VAR WeekFilter = TREATAS ( GENERATESERIES ( Maxweek - 3, MaxWeek ), Week[Week No] )
// Convenient way of generating list of last 4 weeks
RETURN COUNTROWS ( FILTER ( Store, CALCULATE ( SUM ( DATA[sales] ), ALL ( Week ), WeekFilter ) = 0 ) )
Regards,
Owen
Thank you for taking the time and helping me out! The numbers on my huge dataset are correct, and your method of solving this makes perfect sense, and frankly, I feel a little silly for not thinking of it earlier.
This was a massive help. Thank you!
Thank you for your response, and I'm sorry for not sharing sooner.
https://drive.google.com/file/d/1jC2cg5XwLxyi24x2jW-0yUu8qLGlfuxc/view?usp=sharing
This will point you to the file.
Thanks again - I will try to work out a way with your solution.
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |