Forum Discussion
Dynamic P&L Matrix In PBI with calculated rows
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.
YES!! I got the expected result! Thank you so much!!! - Kristi
Hi, Anonymous
I failed to open your link. Could you please follow the prompts for example files?
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.
Sorry about this. This is my first time posting here. Please try this link: https://www.dropbox.com/scl/fi/ekbu425iatvpcwrw0hd62/PowerBI_Dynamic-P-L-Test.pbix?rlkey=dx1snhkbod5b2nevhjoa9y5ak&st=23mrp2wi&dl=0
Hi, Anonymous
You can try the following methods.
Operating Revenues = CALCULATE(SUM('P&L Values'[Value]), FILTER(ALL('P&L Values'),'P&L Values'[Account Order]="1 - Revenues"))Operating Expenditures = CALCULATE(SUM('P&L Values'[Value]), FILTER(ALL('P&L Values'),'P&L Values'[Account Order]="2 - Expenditures"))Gains/Losses = CALCULATE(SUM('P&L Values'[Value]),FILTER(ALL('P&L Values'), 'P&L Values'[Account Order]="4 - Gains/Losses"))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 - Total 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.
I appreciate the suggestion. The result I am expecting is for "3 - Net Operating Income/(Loss)" to be 3,000, not 0. It is a function of the Operating Revenues less Operating Expenditures (5,000 less 2000). In my data set, Expenditures is presented as a negative number so the true formula here is Operating Revenues + Operating Expenditures.
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.