May 15, 2015 at 5:27 pm #429
Hi – I would appreciate some help on finding max sales per day (any time period) for the best selling store. I have got [Sales units on Max Day] covered via the site, however, showing this for the best selling store is proving tricky.
DavidMay 15, 2015 at 5:59 pm #430
What formulas have you tried so far?May 16, 2015 at 5:26 pm #449
I wanted to thank you for the reference to the post on powerpivotpro.com to calculate “Sales Units on Max Day”. I looked up the post (enjoyed it thoroughly) and have assembled formulas along the same line of thought for you to try out:
Best Selling Store :=
MAXX ( VALUES ( Stores[Store] ), [Sales Units] )
Sales Units on Max Day for Best Selling Store :=
[Sales Units on Max Day],
FILTER ( Stores, Store = [Best Selling Store] )
To help others following this conversation and who could offer food for thought, the link to the referenced post is:
Which gives these, as well as other, measures:
[Sales Units] =
[Sales Units on Max Day]=
MAXX(VALUES(Calendar[Date]), [Sales Units])
Also wanted to reference another cool site that “doubles” as a free DAX formatting service and a syntax checker:May 17, 2015 at 5:24 pm #461
Thank you Tom for the post\advice
[Best selling store] would need to return a store code to be used as a filter condition in [Sales Units on Max Day for Best Selling Store], however, the output of [Best selling store] is the max of sales units. The method for [Sales Units on Max Day for Best Selling Store] is super, but my pretty basic skills tell me the problem is 99% solvedMay 19, 2015 at 3:17 am #467
Did you find a solution to your questions?
I found something that worked, although it was not as simple as I first thought. If you found simple solution, please let me know.May 21, 2015 at 9:52 pm #510
The solution is within reach…my post on May 17th is the current positionMay 21, 2015 at 10:07 pm #511
May have solution for you, but need to know if [Best Selling Store] would be static (could be a calculated column) for the data model or would be dynamic (must be a measure).
Also, do you have Excel 2010 or Excel 2013?(2013 offers an option that 2010 does not)May 21, 2015 at 10:12 pm #512
am using Excel 2013 64 bit
Best selling store would be dynamic as influenced by selection of sales date range filter. Store 123 could be best seller one week but not nextMay 21, 2015 at 10:26 pm #513
My solution was based on a static best selling store. But Power Pivot in Excel 2013 offers the option of a “reverse-linkback” table that may have a fit here.
So before I tackle that, let me ask are you looking a “week-by-week” analysis initially of best-selling-store and the best-selling-store’s best selling day in that week?May 21, 2015 at 10:31 pm #514
the time period can vary, I may want to look at a week(min) or upto 8 weeks
the goal is to see the max sales volume per day for the best selling store in the time periodMay 22, 2015 at 8:13 pm #516
Attached is a demo workbook for your review; its measures were designed to answer these questions:
What is the StoreID with top (max) sales in a period (period based on context)?
What is the date of top sales for the StoreID that had the top overall sales in a period (period based on context)?
What was the sales volume on the top sales date for the StoreID with the top overall sales?
There are some other measures, hidden from client tools, that are either components in the measures described above or were of interest during development.
I found a disconnected, linkback table necessary to accomplish the task goals. Initially, I thought identify the store, identify the date and just use them as filter on the Sales table and then the task would be over. Once the linkback table was implemented, my testing showed correct total for the final calculation.
A couple of comments: The sales volume in my model is small, but the measures should work regardless of volume. Also, in this model sales is based on quantity, but is adaptable to currency.
To avoid too long a post here, there is not a discussion of how some of the measures work; if you are interested, I will be explain (could attach a word document).
Your comments are welcome.
Attachments:You must be logged in to view attached files.May 24, 2015 at 4:53 pm #519
this is getting me closer to the answer
1 – I have not used linked back tables before, but I can see how I can achieve the same thing in the Access DB (I am using to source my data) by performing a aggregation in a query and adding this to the . MY table would include the product id as well as order date, store, sum of quantity
2 – on the demo pivot table timeperiod would be a report filter, and product id would be a row fieldMay 24, 2015 at 6:25 pm #521
Yes, you can create a similar aggregated table using Access to do the same as the linkback table.
I believe you will still need to have a disconnected table in order to get the maximum quantity for the max store on the max day. Even when I aggregated sales down to daily levels for store, and could capture the best store and the best day for that store, attempts to apply those results as formula filters on the same table for that daily quantity did not produce results with the correct granularity.
Just so I can stay on the same page as you, I currently understand that your pivots are going to have product id (or a unique product name) on rows, and for that product you are going to have (given a filtered date range), the top selling store for that product, the top sales day for quantities sold of that product at the top selling store, and the quantity sold of that product on the top sales day (for that product) at the top selling store.
Regarding use of the report filter, what would you think of having two slicers which would have “start date” on one and “end date” on the other (or in your case, maybe “Week starting” on one and “week ending” on the other).
Out of curiosity, how do you create your calendar table (do you use Access, Power Query, an external service, etc.)? I am thinking along the lines of adding (if it is not there already) a week ending date for each row in the calendar table (maybe you have such a column already).May 25, 2015 at 3:10 pm #528
the slicers for the time period would be the icing on the cake…
The calendar table is native to Access and then linked via a connection, I have the week ending date in the table, it is a Saturday.
Back to the main problem – avoiding use of link back tables would be good, I am not yet convinced that they are the only solution, because the link back table would have same granularity as the sales table, ie store, sku, date, sum sales
Attached is a one page word doc with my sample pivot and indication of errors.
[Best sell store ID] gives inconsistent results. It is an input for [Sales Units on Max Day for Best Selling Store] and this gives correct results.
It is an input for [Max Sales Units Date for StoreID with Max Sales in Period] that gives inconsistent results.
Attachments:You must be logged in to view attached files.May 25, 2015 at 4:49 pm #530
Thank you for the time to prepare the word document; it is food for thought.
When “[Best sell store ID] gives inconsistent results”, is that because of ties or does it sometimes produce results that are just wrong?
Would you be able to provide sample data? It could be fictitious (SKUs, dates, amounts and store IDs modified).
You must be logged in to reply to this topic.