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

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

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.