Return top selling store by product

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

This just seems too obvious, but I am having a hard time coming up with a way to write this in a measure.  Any help to point me in the right direction would be greatly appreciated!

————————

Example:

If I have say 2 store locations (1 and 2), and 2 products (A and B), I want to know in a measure which store sells the most.

i.e. Store 1 sells [10 of product A] and [5 of product B]

Store 2 sells [3 of product A] and [7 of product B]

I would like a measure to be able to show me in a pivot (pivoted by product):

Product              Best selling Store

A                                     1

B                                     2

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

See attached Excel 2013 workbook; if you have Excel 2010, let me know and I will attach an Excel 2010 workbook so you can access the Power Pivot window.

I have also pasted the formula for best selling store on the same worksheet as the pivot (you should at least be able to open the workbook, whether you have 2010 or 2013).

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom you rock.  That’s brilliant.  Not everyday you see a measure that returns text you can use in the value box 🙂

Are you doing much with Power Query? BI.COM?

 

Thanks again!

 

 

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom,

One more quick question.  If there is a TOPN.. is there also a BOTTOM type function?  We also would like to return the ‘least’ selling store.

Thanks again!

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Mark,

TOPN also handles cases where you are looking for “BOTTOMN” by setting an optional parameter to 1; attached workbook shows how to do this with a measure for “Least Selling Store”. Both measures now handle ties for best or least selling store. Have also included a measure for “Only Store Selling Product”.

Just for conversation, have also added a couple of pivots (one of which shows store and quantity where product had sales and the other pivot shows stores where there no sales of product).

Another question that could be asked with different data: Which stores stocked product (or did not stock product)?

Regarding Power Query, I use it regularly when I use Excel 2013, but try to avoid it when using 2010.

Regarding Power BI (powerbi.microsoft.com), which has just come out of preview, I am building up my skills there because it is an extension to (not a replacement for) what I do with Power Pivot and Power Query.

Hope this helps.

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks Tom for all those measures; they will keep me busy for a while.  I re-created all of them in my model and will let them sink in 🙂

Thanks for commenting on Query and BI.  I just tried Query the other day in 13 and definitely see the value in using it regularly.

 

Thanks again!!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks so much Tom for the workbook you sent.  I have what I hope is just a quick follow up…

I thought I would be able to create a pivot that would show me Store# and Qty sold AND also include the ‘LeastStore’ measure in the same pivot report.

I put an ALL(store) filter within the LeastStore Calculate, but it does not seem to look past the current location being filtered in the pivot?

The file attached shows the new pivot and what I’m trying to report.  For example, Product A should return store 3 as the Least, regardless of current store filtered?

I am used to using ALL in my Calculates, is there a different way to accomplish this?

Many thanks for taking a look at this,

Mark

 

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Mark,

Take a look at the ALLEXCEPT function and see if it offers the answer you are looking for.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom,

I ended up being able to create a measure that un-dos the filter in the pivot, allowing for a comparison of the current store’s qty sold vs. another (CALC ALL) column showing the least selling store regardless of current store in the filter.  Thanks for the help on all of this.

Also took your advice and checked out ALLEXCEPT on the blog posts.  I read the three part series that compares ALL vs. ALLEXCEPT.  Also read up again on IF VALUES.  This stuff isn’t natural to me (like Excel formulas) quite yet, but I am definitely seeing the similarities when coming up with logic formulas.

Thanks again!

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Mark,

Glad to hear of your success.  Really enjoyed working with the you and the different formulas.

Do not be intimidated though by the learning curve.  I like about Rob Collie’s approach to the learning curve:  we can enjoy the curve because climbing it makes us better.

One thing that I keep rediscovering is that formulas can also evolve as we learn more about Power Pivot and work requirements.  For example, in the Power Pivot measures we worked on, the quantities were all positive, so the FILTER functions that used the criteria “> 0” were alright, but perhaps “<> 0” (not equal to 0) would be better if store returns (expressed as negative quantities) of the individual products are ever included in your data.  Anyways, something to think about.

Remember that you can always ask your Power Pivot, Power Query and Power BI questions here.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Great points.  thanks again for taking the time. branching out to dax as an excel user, this site and approach works best for me.

 

ill be back 🙂

[Author]

Posts