Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    YES!! I got the expected result! Thank you so much!!! - Kristi

Anonymous's avatar
Anonymous
Not applicable
1 year ago
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)

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,
    BLANK()
)
gmsamborn's avatar
gmsamborn
Super User
1 year ago

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)