Forum Discussion
Divide by subtotal
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.
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 !
Hi Anonymous , thanks for replying, I'll take a look at those two options.
4 Replies
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 !