PQ: Calculating largest items in a list

[cr731]

I am trying to use PQ to calculate the largest items in various columns in my table and looking for a more efficient solution. I have a table with a list of item numbers and associated brand, channel, and sales values, similar to below

Item	Brand	Channel	Sales
Item1	Brand1	Channel1	10
Item1	Brand1	Channel2	25
Item1	Brand2	Channel3	30
Item1	Brand3	Channel2	40
Item2	Brand5	Channel1	50
Item2	Brand2	Channel4	60

<tbody>
</tbody>

I'm looking for the largest "Brand" and "Channel" within each Item (based on sum of sales). So if I do this manually, I would create two additional pivot tables to summarize by Item-Brand and Item-Channel, like this

Item	Brand	Sum of Sales
Item1	Brand3	40
Item1	Brand1	35
Item1	Brand2	30
Item2	Brand2	60
Item2	Brand5	50
Grand Total		215

<tbody>
</tbody>

Item	Channel	Sum of Sales
Item1	Channel2	65
Item1	Channel3	30
Item1	Channel1	10
Item2	Channel4	60
Item2	Channel1	50
Grand Total		215

<tbody>
</tbody>

I would then create a table with just unique item numbers, and use Index-Match or Vlookup to return the top items in each list.

Item	Top Brand	Top Channel
Item1	Brand3	Channel2
Item2	Brand2	Channel4

<tbody>
</tbody>

Clearly, not very efficient and also I frequently need to add/remove columns (i.e. I might be looking for Brand/Channel today but next month need to add something else like customer or market).

I feel as if Power Query could help me here but I am struggling to find an efficient way. The best thought I have so far would be to load the table to PQ, then group by Item-Brand, sort descending by sales, and then remove duplicates. But this is still inefficient because I would need one query for every field I want to find the top item. It would be great to have it all in one query.

Hoping someone might know of a better way.

Thanks

 

[Matt Allington]

Are you open to doing it in Power Pivot?

 

[cr731]

Are you open to doing it in Power Pivot?

I'm open to anything that's more efficient than my manual method.

 

[Matt Allington]

OK, here are a couple of working solutions

Firstly using your data model design. I have built interim measures so you can see how it works (displayed on the left), however the correct Top Brand is returned on the right regarless if you have items in the pivot.

This works, but you need a hard coded set of measures for each attribute you want to analyse (ie Brand, Channel).

I came up with this alternative approach

What I did in this case was to duplicate the data using Power Query - there is one set of rows where Attribute Type = Channel and another set of rows where Attribute Type = Brand. The advantage of this approach is that I could write a single measure that reacts to a slicer as shown above. This is scalable. The downside is you have to multiply your rows of data x N for N attributes and you have to write the Power Queries to do it.

Here is the workbook https://www.dropbox.com/s/j3sz3r6nyy2czrq/rank brand.xlsx?dl=1

 

[citizenbh]

I have had another approach.
Everything was done in PowerPivot.
Two measures:

SumBrand:=CALCULATE (SUM([Sales]);ALLEXCEPT(Table1;Table1[Brand];Table1[Item]))
SumChannel:=CALCULATE (SUM([Sales]);ALLEXCEPT(Table1;Table1[Channel];Table1[Item]))

and two Pivot Tables.

In both tables perform
By sorting Z ↓ A column TopBrand and TopChannel
and add
Value Filter Top 10 = 1

The columns I and J are hidden


Hier is the file: https://www.dropbox.com/s/azkna7qujm4p61g/Calculating-largest-items-list.xlsx?dl=0