Forum Discussion

Msampedro's avatar
Msampedro
Icon for Helper I rankHelper I
1 year ago
Solved

P&L based on a Table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Msampedro
Icon for Helper I rankHelper I
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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!