Percentage per Group

[joshua1990]

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?

[Fowmy]

@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]
        )
    )

[joshua1990]

@Fowmy : Thanks, but I have posted this question within the PQ area to get it resolved using PQ.

Any further ideas?

[Fowmy]

@joshua1990 

Sorry about that. Please clarify you need the Percentage of sales by 
 Year, Month and Department ?

In this case, product will not have a break down. so your matrix should look like 

Year

Month

Department

Sales%

[wdx223_Daniel · Accepted solution]

NewStep=Table.Combine(Table.Group(PreviousStepName,{"Year","Month","Department"},{"n",each let a=List.Sum([Sales]) in Table.AddColumn(_,"Percentage",each [Sales]/a)})[n])