Forum Discussion
Percentage per Group
Hi experts!
I have a table with the year, month and department information.
In addition to that I have sales per product:
| Year | Month | Department | Product | Sales |
| 2023 | 01 | A | A1 | 5 |
| 2023 | 02 | A | A2 | 60 |
| 2023 | 10 | B | B1 | 40 |
Now I would like to get the percentage of the sales for each product for each YearMonthDepartment combination / group.
How would you do that?
NewStep=Table.Combine(Table.Group(PreviousStepName,{"Year","Month","Department"},{"n",each let a=List.Sum([Sales]) in Table.AddColumn(_,"Percentage",each [Sales]/a)})[n])
4 Replies
joshua1990
Add these columns in a matrix Row section and the following measure in the value.
ROW Section:
'YourTable'[Year],
'YourTable'[Month],
'YourTable'[Department],
'YourTable'[Product]Sales Percentage = DIVIDE ( SUM ( 'YourTable'[Sales] ), CALCULATE ( SUM ( 'YourTable'[Sales] ), ALLEXCEPT ( 'YourTable', 'YourTable'[Year], 'YourTable'[Month], 'YourTable'[Department], 'YourTable'[Product] ) )Fowmy : Thanks, but I have posted this question within the PQ area to get it resolved using PQ.
Any further ideas?
NewStep=Table.Combine(Table.Group(PreviousStepName,{"Year","Month","Department"},{"n",each let a=List.Sum([Sales]) in Table.AddColumn(_,"Percentage",each [Sales]/a)})[n])
