Divide by subtotal

[ScottF75]

Hi,

I'm trying to find an item's share of a particular value.

I have item sales across multiple locations and footprints within these locations i.e. locations 1 and 4 have the same footprint and locations 2 and 3 have the same footprint. This sales data is all in the same column with the Location and footprint also in separate columns.

Within Power Query, I would like to add a column that will show the sales share of each item within the location and footprint they belong to and i'd like the column to be part of the same table.

Thanks for looking.

[crm2710 · Accepted solution]

Hello @ScottF75 , if I'm interpreting this correctly, it sounds like a "% of Group", class of issues in power query. I had a similar issue a few weeks ago and unfortunately, there is no 'straightforward' way that I could find. The method I used came from the following Youtube clip I found handy, involves Grouping the table a couple of times and making some edits.

This will give you what you need ( a 4th column showing the % as part of the same table): i.e. Location, Footprint, Sales, % of Group . 

As an alternative, there is the following approach which doesn't involve grouping as much but which does need editing to the M query (both end up at the same result but from different approaches). 

Hope this helps and good luck, I know its a bit of effort !

[crm2710 · Accepted solution]

Hello @ScottF75 , if I'm interpreting this correctly, it sounds like a "% of Group", class of issues in power query. I had a similar issue a few weeks ago and unfortunately, there is no 'straightforward' way that I could find. The method I used came from the following Youtube clip I found handy, involves Grouping the table a couple of times and making some edits.

This will give you what you need ( a 4th column showing the % as part of the same table): i.e. Location, Footprint, Sales, % of Group . 

As an alternative, there is the following approach which doesn't involve grouping as much but which does need editing to the M query (both end up at the same result but from different approaches). 

Hope this helps and good luck, I know its a bit of effort !

[ScottF75]

@crm2710 That YouTube video is exactly what i've been looking for, thank you.

[crm2710]

That's great! I'm glad the video was of help - all the best!

[ScottF75 · Accepted solution]

Hi @crm2710 , thanks for replying, I'll take a look at those two options.