Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
2 years ago
Solved

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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

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

Fowmy's avatar
Fowmy
Super User
2 years ago

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's avatar
joshua1990
Post Prodigy
2 years ago

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

Any further ideas?

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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%