Forum Discussion
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]
)
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
- Anonymous1 year ago
Hi Yulia,
Thanks for your feedback. I have solved the issue by using ALLEXCEPT.
Cheers!
3 Replies
- AnonymousNot applicable
Hi Yulia,
Thanks for your feedback. I have solved the issue by using ALLEXCEPT.
Cheers!
- AnonymousNot 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
- AnonymousNot 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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.