topic Re: Divide by subtotal in Power Query

Divide by subtotal

ScottF75 — Mon, 19 Feb 2024 12:00:44 GMT

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.

Re: Divide by subtotal

Anonymous — Mon, 19 Feb 2024 16:52:27 GMT

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 !

Re: Divide by subtotal

ScottF75 — Mon, 19 Feb 2024 16:57:01 GMT

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

Re: Divide by subtotal

ScottF75 — Mon, 19 Feb 2024 17:02:29 GMT

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

Re: Divide by subtotal

Anonymous — Mon, 19 Feb 2024 17:03:15 GMT

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