P&L based on a Table

[Msampedro]

Hola,

Necesito crear un P&L en base a una tabla dónde tengo valores como Year, Month, Net Sales, Variable COGS, Variable Contribution, Fixed COGS ... He procedido a hacer Unpivot columns de (Net Sales, Variable COGS, Variable Contribution, Fixed COGS...). Tambien he creado dos "Custom columns" el VM% = variable contribution/net sales y el GM % = Gross profit/net sales. 

Cuando creo un Matrix para reflejar en diferentes líneas todos los campos del P&L, los valores cálculados VM% y GM%, aparecen incorrectos. Lo que busco es el % total de cada año. Debajo podréis ver el estado actual.

Thanks,

Miguel

[Msampedro]

Hi @burakkaragoz @Anonymous @Khushidesai0109 ,

Sharing an example of my current table, after unpivoted needed attributes (Net Sales, Gross Profit...). From this I need to calculate VM% and GM% to include them in a Matrix, as a single row (see what I am looking for on my second capture). Many thanks in advance.

[Anonymous · Accepted solution]

Hi @Msampedro ,
Thank you for more provideing more details.I would be happy to assist you!

Please include below measures :

PnL Value =

VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])

VAR NetSales = 

    CALCULATE(

        SUM('Unpivoted Table'[Value]),

        'Unpivoted Table'[Attribute] = "Net Sales"

    )

VAR VarContribution = 

    CALCULATE(

        SUM('Unpivoted Table'[Value]),

        'Unpivoted Table'[Attribute] = "Variable Contribution"

    )

VAR GrossProfit = 

    CALCULATE(

        SUM('Unpivoted Table'[Value]),

        'Unpivoted Table'[Attribute] = "Gross Profit"

    )

RETURN

    SWITCH(

        TRUE(),

        SelectedAttribute = "VM %", DIVIDE(VarContribution, NetSales),

        SelectedAttribute = "GM %", DIVIDE(GrossProfit, NetSales),

        CALCULATE(

            SUM('Unpivoted Table'[Value]),

            'Unpivoted Table'[Attribute] = SelectedAttribute

        )

    )

PnL Value (Formatted) =

VAR BaseValue = [PnL Value]

VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])

RETURN

    SWITCH(

        TRUE(),

        SelectedAttribute IN {"VM %", "GM %"}, FORMAT(BaseValue, "0.00%"),

        FORMAT(BaseValue, "#,##0")

    )

Then add a matrix viual.For rows,add PnL Structure[Attribute], for Column add Month or Year upon requirement and for values add PnL Values(formatted) measure.
Please refer the screenshot and file for your reference.

If this answer meets your requirements,consider accepting it as solution.If still facing the issues,feel free to reachout!

Thank you.

[Msampedro]

This is working, many thanks!! Already accepted as a solution. 

One additional question, I am adding one more Attribute/calculation - Do you know How I can give decimal formar? I have tried this one but not working (syntax error) 

VAR QtyLiter =

    CALCULATE(

        SUM('P&L'[Value]),

        'P&L'[Attribute] = "Qty (Liter)"

    )    

RETURN

    SWITCH(

        TRUE(),

        SelectedAttribute = "VM %", DIVIDE(VarContribution, NetSales),

        SelectedAttribute = "GM %", DIVIDE(GrossProfit, NetSales),

        SelectedAttribute = "ASP, EUR/lt", DIVIDE(NetSales, QtyLiter),

        CALCULATE(

            SUM('P&L'[Value]),

            'P&L'[Attribute] = SelectedAttribute

        )

    )      

[Anonymous]

Hi @Msampedro ,
Thank you for the folow-up question!

The logic looks good, but the issue might due to SelectedAttribute being used without being defined inside your measure. You may need to declare it as a variable first.

II hope this helps! If you are still experiencing the same issue, please feel free to raise a new thread — we will be happy to assist you further.

Thank you for your understanding!

[Khushidesai0109]

Hola @Msampedro 

VM % =
DIVIDE(
SUM('Table'[Variable Contribution]),
SUM('Table'[Net Sales])
)

GM % =
DIVIDE(
SUM('Table'[Gross Profit]),
SUM('Table'[Net Sales])
)

Luego, en tu matriz:

• Coloca Attribute en las Filas

• Coloca las nuevas medidas (VM %, GM %, etc.) en los Valores

• Agrega Año o Mes en las Columnas o como filtro, según sea necesario

[burakkaragoz]

Hi @Msampedro ,

The issue you’re facing with the percentages (VM% and GM%) in your P&L matrix is a common one in Power BI when working with unpivoted tables. When you use calculated columns for percentages, Power BI aggregates the numerators and denominators separately, which can lead to incorrect percentages at higher levels of your matrix.

Recommended Solution:
To get the correct percentage for each row relative to the total, you should use a DAX measure instead of a calculated column. This way, Power BI calculates the percentage dynamically based on the context of your matrix. For example:

dax

VM% P&L = DIVIDE(
    CALCULATE(SUM('Table'[Variable Contribution])),
    CALCULATE(SUM('Table'[Net Sales]))
)

dax

GM% P&L = DIVIDE(
    CALCULATE(SUM('Table'[Gross Profit P&L])),
    CALCULATE(SUM('Table'[Net Sales]))
)

With these measures, Power BI will recalculate the numerator and denominator according to your matrix filters, showing the correct percentage at every level.

Steps:

1. Create these measures in your model.
2. Use the measures in your matrix instead of custom columns.
3. If you need to display the % of the overall total, make sure your matrix context allows it (for example, by using "Show values as % of grand total").

Useful Reference:

• DAX Basics in Power BI (Microsoft Docs)

If you need more detailed DAX examples based on your model, just let me know!
If this helps, please consider marking as solution and giving kudos to help others in the community.

Thank you!
translation and formatting supported by AI

[Msampedro]

Hi @burakkaragoz @Khushidesai0109 

Thanks for your response.

Unfortunatelly, I believe this is not working since after DAX measures done (VM% and GM%) I have to unpivot some columns (Net sales, Gross profit, var contribution etc...) to get Attributes and build my P&L using a Matrix. After unpivot those columns, the measures do not work as they cannot find columns like Net sales, Variable contirbution...

Formula error message --> Net Sales in Table 'P&L' cannot be found or not may be used in this experssion.

Many Thanks,

Miguel

[Anonymous]

Hi @Msampedro ,
Thank you @Khushidesai0109  and @burakkaragoz for the detailed information!

Upon my understanding,I tried to recreate it on my local witht the sample data.Please find the screenshot and attached file for your reference.

If this answer meets your requirements,give us kudos and consider accepting it as solution.

Regards,
Pallavi G.