Dynamic P&L Matrix In PBI with calculated rows

[Anonymous]

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. 

P&L Values =

VAR Revenue = COALESCE([Operating Revenues], 0)

VAR Expenditures = COALESCE([Net Operating Income], 0)

VAR NetIncome = [Operating Revenues] - [Operating Expenditures]

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('Account Order Table'[Account Order]) = "3 - Total Net Operating Income/(Loss)",

        CALCULATE(NetIncome, ALLSELECTED('Account Order Table')),

    SELECTEDVALUE('Account Order Table'[Account Order]) = "1 - Revenues",

        Revenue,

    SELECTEDVALUE('Account Order Table'[Account Order]) = "2 - Expenditures",

        Expenditures,

    BLANK()

)

LINK TO EXAMPLE FILE: https://www.dropbox.com/scl/fi/ekbu425iatvpcwrw0hd62/PowerBI_Dynamic-P-L-Test.pbix?rlkey=dx1snhkbod5...

This is very simple fake data for the purpose of understanding how to get this to work.

[Anonymous · Accepted solution]

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]

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.

[gmsamborn]

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]

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]

@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)

[Anonymous]

LINK TO EXAMPLE FILE: https://drive.google.com/file/d/1H-63CxEb3M8w4sVfItD3BzsqtJ9etpyJ/view?usp=sharing

This is very simple fake data for the purpose of understanding how to get this to work.

Thank you!

[Anonymous]

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.

[Anonymous]

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=dx1snhkbod5...

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous · Accepted solution]

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 · Accepted solution]

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