How to Calculate SUM of values that meets a condition and more ...

[Power007]

Hello Power BI gurus,

I am struggling with a few things here. And I have to admit I'm also struggling with how to describe the scenario.  Anyway, partial list of the data I'm working on is attached below. Also, the desired output (Matrix) is also attached below. (Please note that the numbers represent the entire data, and not just a subset.)

1) I need a Measure to Calculate Prior Budget.  It should be a sum of all values where BT Class 2 - "PRIORB"

2) I need a Measure to Calculate Ending Budget.  It should be Prior Budget minus Net Debit minus Net Credit.

3) Why is Total for Net Debit and Net Credit incorrect?

Here are the DAX used:

Net Credit = IF(Sum('X_GL_TRANSACTION_DETAIL'[Debit]) < Sum('X_GL_TRANSACTION_DETAIL'[Credit]), Calculate (Sum('X_GL_TRANSACTION_DETAIL'[Credit])) - Calculate (Sum('X_GL_TRANSACTION_DETAIL'[Debit])), Blank())

Net Debit = IF (Sum('X_GL_TRANSACTION_DETAIL'[Debit]) < Sum('X_GL_TRANSACTION_DETAIL'[Credit]), Blank(),Calculate (Sum('X_GL_TRANSACTION_DETAIL'[Debit])) - Calculate (Sum('X_GL_TRANSACTION_DETAIL'[Credit])))

Thank you very much.

[v-jialluo-msft]

Hi  @Power007 ,

You can try this:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Gallen Luo

[Power007]

Hi Gallen,

Here's the link to the sample data and desired output.

https://we.tl/t-5HvKMWAC4O

Thank you,

Carlito

[v-jialluo-msft]

Hi  @Power007 ,

According to your description, you need to do some measurement operations.

You may need to use the ALL function to remove filtering of the data, please provide sample data and expected output to further solve the question.

Best Regards,
Gallen Luo

[Power007]

Thank you for looking into this.  My problem is I don't know how to attach sample data file here.

Please advise.

[Power007]

Correction:

2) Ending Budget = Prior Budget plus Net Debit minus Net Credit