Home Forums Power Pivot max sales per day single store out of 500

Tagged: ,

This topic contains 24 replies, has 2 voices, and was last updated by  tomallan 3 years, 2 months ago.

Viewing 15 posts - 1 through 15 (of 25 total)
  • Author
    Posts
  • #429

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    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.

    Thanks

    David

    #430

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    What formulas have you tried so far?

    #449

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    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 :=
    CALCULATE (
    [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:

    http://www.powerpivotpro.com/2012/06/finding-the-top-selling-date-product-etc/

    Which gives these, as well as other, measures:

    [Sales Units] =
    SUM(Sales[OrderQuantity])

    [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:

    http://www.daxformatter.com/

    #461

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    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% solved

    #467

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    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.

    #510

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    Hi Tom

    The solution is within reach…my post on May 17th is the current position

    #511

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    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)

    #512

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    Hi Tom,

    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 next

    #513

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    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?

    #514

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    Hi Tom,

    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 period

    #516

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Hello David,

    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.
    #519

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    Hi Tom,

    this is getting me closer to the answer

    Note:

    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 field

    #521

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

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

    #528

    davidrobsmith
    Participant
    • Started: 2
    • Replies: 11
    • Total: 13

    Hi Tom,

    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.
    #530

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    David,

    Thank you for the time to prepare the word document; it is food for thought.

    Questions:

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

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic.