Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Vertical P&L Special Calculations

Hello Everyone, 

 

I hope someone can help me with this DAX or logic...I am trying to build a P&L report in Matix with 4 P&L levels in rows.  Some of the P&L components are not simply the sum of the sub-group in the lower level. 

For example:

 

Trade Spend = "Invoice Disc" + "Non-Inv Dis" (excluding "Inv Sales - Ext")
NSV - Ext = "Inv Sales - Ext" - "Non-Inv Dis"

Those P&L components are not from GL transactions.

So, I have built a dynamic P&L measure, aiming to redirect the returning value to their specific measures. 

Dynamic P&L Measure Testing =
VAR IsLevel1 = ISINSCOPE('MappingTable'[P&L Level 1])
VAR IsLevel2 = ISINSCOPE('MappingTable'[P&L Level 2])
VAR Level1Value = SELECTEDVALUE('MappingTable'[P&L Level 1], "Other")
VAR Level2Value = SELECTEDVALUE('MappingTable'[P&L Level 2], "Other")

RETURN
SWITCH (
TRUE(),
IsLevel2 && Level2Value <> "Other", SWITCH(
Level2Value,
"Inv Sales - Ext", [Inv Sales - Ext Actuals],
"NSV - Ext", [NSV - Ext],
[Actuals]
),
IsLevel1 && Level1Value <> "Other", SWITCH(
Level1Value,
"Trade Spend", [Trade Spend],
"NSV", [NSV],
[Actuals]
),
[Actuals]
)

 

Inv Sales - Ext Actuals =
VAR GSV =
    CALCULATE(
        [Actuals],
        ALL('MappingTable'[P&L Level 1]),  // Removes filters from P&L Level 1
        'MappingTable'[P&L Level 1] = "GSV"
    )
VAR InvoiceDisc =
    CALCULATE(
        [Actuals],
        ALL('MappingTable'[P&L Level 2]),  // Removes filters from P&L Level 2
        'MappingTable'[P&L Level 2] = "Invoice Disc"
    )
RETURN
    GSV - InvoiceDisc


I don't think my logic is correct. The Inv Sales - Ext Actuals measure return the correct amount in a Card, but in my Matrix with 4 levels, it only shows the amount of InvoiceDisc.....

Also I have another concern that the special calculation P&L components at the bottom of the report refer to the other measures from the top of the P&L. Those self-reference measures may not work....any thought?

Appreciate any comment and advice. Cheers!

 

Sam

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Yulia, 

     

    Thanks for your feedback. I have solved the issue by using ALLEXCEPT.

     

    Cheers!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Yulia, 

     

    Thanks for your feedback. I have solved the issue by using ALLEXCEPT.

     

    Cheers!

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Anonymous 

       

      Glad to hear you solved the problem yourself! Could you please mark your solution as an ANSWER? This will help more users who are facing the same or similar difficulties. Thank you!

       

      Best Regards,
      Yulia Xu

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous 

     

    Could you please provide some sample data and expected results based on the sample data? So that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.


    In addition, I noticed that [Actuals] in your formula seems to be a measure. If my judgment is correct, could you please give the calculation logic of this measure?

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.