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 10 posts - 16 through 25 (of 25 total)
  • Author
    Posts
  • #531

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

    Hello David,

    Please ignore request for sample data; I have found a dataset that I can use.

    #534

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

    David,

    Topic: Modified Calculation for [Best sell store ID]

    The formula for [Best sell store ID] in the MS Word document was written to give you the best selling store ID over a period, and, because of how the formula is written, the answer for the week can be different from the best selling store for a single day.

    For example, store “A” spikes for SKU 222 on Wednesday with 11 units, but for each of the other days of the week it sells 6, but store “B” sells steadily at 8 units for all days of the week. Store “A” has the best daily sales for the week, but Store “B” has better sales when considering all days of the week.

    But if you want a calculation that for the weekly subtotal will give you the store that had the best day of the week, the following should do that (it is almost identical to the current formula, except that it also groups by trx_date):

    Best sell store ID=FIRSTNONBLANK (
    SUMMARIZE (
    TOPN (
    1,
    SUMMARIZE (
    t_retail_sales,
    [store],
    [trx_date],
    “StoreSalesByQuantity”, SUM ( ‘t_retail_Sales'[Quantity] )
    ),
    [StoreSalesByQuantity]
    ),
    [store]
    ),
    [store]
    )

    Please test and let me know if it meets your needs.

    #542

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

    Hi Tom,

    I copied and pasted the above formula resulting in an error – is the syntax right? I notice the apostrophes on SUM ( ‘t_retail_Sales'[Quantity] ).

    Also, should SUM ( ‘t_retail_Sales'[Quantity] ) have a calculate wrapped around it?

    #545

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

    David,

    The syntax is correct for my data model, where I tried to duplicate your column names (but it is possible that I misspelled one of the column names).

    Other than a spelling difference in my model, one other issue is that sometimes when copying formulas into a web site, apostrophes and quotation marks become “smart” apostrophes and quotation marks which changes their value.

    To offset the possibility of an issue with the copy/paste process on my side, I have attached a copy of a test workbook:

    Test New Best Sell Store ID Formula.xlsx

    where I was testing the formula and you can remove it directly from the workbook Please note that in this test workbook the formula is called:

    new Best sell store ID

    I have not set up the workbook to be presentable to others, it was a testing tool for me as I tried to recreate something like your dataset and work with different formulas. Again, the difference is that the “new” formula has a reference to trx_date.

    If you copy the formula behind “new Best sell store ID”, and you still get an error message, let me know what the error message says and we will resolve it.

    Attachments:
    You must be logged in to view attached files.
    #547

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

    David,

    Another idea just came to me.

    When I write a DAX formula, I use the “:=” syntax after the formula name and your formulas use the “=” syntax. It depends on where you enter your formulas for which syntax to use. So if you enter your DAX formulas in the Calculated Field dialog box, you will want to make sure you remove the colon before the =.

    #3745

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

    Hi Tom,

     

    I cannot see how to post a new topic, but would like to discuss this with you.

     

    I have 24 one hour slots in each day of the year. Each slot has a price for a commodity. I want to create a column to flag the row that has the max price in the day.  So 3 columns, date, hour (1-24), price, and I want to show a fourth column with a flag of 1 or 0 with 1 being the max price in the day.

    Thanks

     

    David

    #3748

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

    Assuming a table of for one date (only 24 rows), and only those three columns, one way to express the formula for the requested calculated column would be:

    Is Max Price for Day = IF ( CALCULATE ( COUNTROWS ( Data ), FILTER ( Data, [Price] > EARLIER ( [Price] ) ) ) = 0, 1, 0 )

    If the table would have a product column and the table could contain many dates, the formula could look like:

    Is Max Price for Product Per Day = IF ( CALCULATE ( COUNTROWS ( Data ), FILTER ( Data, [Price] > EARLIER ( [Price] ) && [Product] = EARLIER ( [Product] ) && [Date] = EARLIER ( [Date] ) ) ) = 0, 1, 0 )

    The above are calculated columns, doing measures would be similar, but not quite the same.

    It should be noted that ties will share the 1.

    Tom

    #3754

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

    thanks Tom – I will apply this

    #3755

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

    Tom – the second formula worked well – I will consult my books on how it thinks. Thx David

    #3756

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

    David,

    Essentially the formula is an alternate to using RANKX. The first part of the formula is asking “Are there any rows with a price greater than this row?” and if the answer is 0, then we have the row with the greatest price. The second part of the formula using more EARLIER() functions is just setting boundaries for the highest price (for this product on this day…). Following this pattern you could add criteria to include store ID.

    A measure would be structured differently. A formula for a measure could be built with checks such as IF ( HASONEVALUE () ) so the VALUES() function could be used instead of EARLIER(). A measure would also use an ALL() within the filter function.
    Glad to see you back in the powerpivotpro forums!

    Tom

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic.