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

gmsamborn's avatar
gmsamborn
Super User
1 year ago

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.

 

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)