Forum Discussion
Dynamic P&L Matrix In PBI with calculated rows
I am trying to create a dynamic P&L matrix in Power BI that will add a calculated row for Net Operating Income/(Loss) - which is Operating Revenues - Operating Expenditures. I have created calculated measures for Operating Revenue, Operating Expenditures, Net Operating Income/(Loss) and Gains/Losses. I used this Switch formula below to create my P&L Values to pull into the matrix. It does not populate values in my calculated row for Net Operating Income/(Loss). I've tried trouble-shooting with Chat GPT. Nothing suggested fixed the issue. At this point I created 2 very simple tables with sample data just to try to get this to work to eventually apply to my actual data set (which is much more complex). My P&L Values table (actual data) is related to my Account Order Table (this is my template data for the P&L set up ) by the category (revenue, expenditures, gains/losses).
I've tried having the Switch formula calculate the Net Op Income and also use a separate calculated field. Neither worked. I hope someone can give me some ideas Chat GPT couldn't come up with.
I should also add that the line "3 - Total Net Operating Income/(Loss)" appears in my Matrix because I seleted "show items with no data" on the Account Order field. I was able to get it to populate a 0.00 in my troubleshooting with chat gpt, but that's not the answer. There is a true value to Revenue - Expenses. I also checked all my filters so it's not excluding any data.
Hi, Anonymous
I changed the "3 - Total Net Operating Income/(Loss)" in your original formula to a "3 - Net Operating Income/(Loss)".
P&L Values = VAR Revenue = COALESCE([Operating Revenues], 0) VAR Expenditures = COALESCE([Net Operating Income], 0) VAR NetIncome = COALESCE([Net Operating Income], 0) VAR GainsLosses = COALESCE([Gains/Losses], 0) RETURN SWITCH( TRUE(), SELECTEDVALUE('Account Order Table'[Account Order]) = "3 - Net Operating Income/(Loss)", NetIncome, SELECTEDVALUE('Account Order Table'[Account Order]) = "1 - Revenues", Revenue, SELECTEDVALUE('Account Order Table'[Account Order]) = "2 - Expenditures", Expenditures, SELECTEDVALUE('Account Order Table'[Account Order]) = "4 - Gains/Losses", GainsLosses,0 )Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
Hi Anonymous
I can see a problem with the line with CALCULATE.
You are using a variable (NetIncome) when an expression is required. Because the variable is evaluated earlier and it can't be recalculated by CALCULATE means that the results will be incorrect.
It *might* be as easy as changing to
CALCULATE( [Operating Revenues] - [Operating Expenditures], ALLSELECTED('Account Order Table'))
but it would be tough to tell without sample data.
Let me know if you have any questions.
- I updated my formula to one I had previously used using a pre-calculated Net Op Income, but it's still not working. 😞 Thanks for reviewing and replying. Is there a way to post the data sets? I'm new to this forum - my first question I've asked. It's just made up data so nothing confidential.P&L Values =VAR Revenue = COALESCE([Operating Revenues], 0)VAR Expenditures = COALESCE([Net Operating Income], 0)VAR NetIncome = COALESCE([Net Operating Income], 0)RETURNSWITCH(TRUE(),SELECTEDVALUE('Account Order Table'[Account Order]) = "3 - Total Net Operating Income/(Loss)",NetIncome,SELECTEDVALUE('Account Order Table'[Account Order]) = "1 - Revenues",Revenue,SELECTEDVALUE('Account Order Table'[Account Order]) = "2 - Expenditures",Expenditures,BLANK())
Anonymous
You can upload xlsx or pbix to one of OneDrive, Google Drive, DropBox, WeTransfer and post the link here. You may have to change sharing rights depending on which you choose.
Also in your DAX both of these variables use the same measure:
VAR Expenditures = COALESCE([Net Operating Income], 0)VAR NetIncome = COALESCE([Net Operating Income], 0)
Hi, Anonymous
Can you provide example files and what output you expect? This will better help you solve the problem. Please remove any sensitive data in advance.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
