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
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.
YES!! I got the expected result! Thank you so much!!! - Kristi