Subtraction Formula doesn´t Work

[EmiliaB_123]

Hi everyone,

I am creating a simple subtraction formula in the Data Query and unfortunately the result is not correct. I think this is due to the "null" values. It only shows result values of the formula when both columns are filled with figures. Those lines are all related to one project. So what I actually need is the sum of one column minus the sum of the second column. Is there a formula that can show this? Please consider that the values are per Project and Period. So it would need to consider the sum values per project and period.

Thank you!

[v-eqin-msft · Accepted solution]

Hi @EmiliaB_123 ,

Please try this:

Column =
CALCULATE (
    SUM ( 'Table'[COST_ESTIMATED] ),
    ALLEXCEPT ( 'Table', 'Table'[Period], 'Table'[Project ] )
) + 0
    - CALCULATE (
        SUM ( 'Table'[COST_ACTUAL] ),
        ALLEXCEPT ( 'Table', 'Table'[Period], 'Table'[Project ] )
    ) + 0

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lovishsood1]

Did you try replacing 'null' values with 0?

[EmiliaB_123]

Hi @lovishsood1 , it was the first thing I tried and it didn´t work.

[lovishsood1]

are you expecting such results?

[Angith_Nair]

Hi @EmiliaB_123 ,

Lets assume this as A - B = C.

Could I please know what you want to display in C when A is null or B is null..?

[EmiliaB_123]

Hello @Angith_Nair ,

thanks for your quick reply. That is actually a good question, if see the values then there should be also negative figures. But if we take the total of A and the total of B there should be only one figure shown as result. I understand that the nulls seem to be a problem, but can´t the system ignore them in some way?

[Angith_Nair]

Do you want the total of A column subtracted with the total of B column and then display that single value in every row..? If yes then you can create a calculated column in Power BI level where the code will be SUM(Table[A]) - SUM(Table[B]).

If No then assume the data like this

A             B           C

12           Null       12

Null         10         10

Is that what you needed..?

[EmiliaB_123]

Hello @Angith_Nair ,

I tried out thir formula but it shows me the sum of all the columns. So there is always the same values shown. As I mentioned this needs to be the sum for one project and one period and not over all.

[Angith_Nair]

Hi @EmiliaB_123 ,

Assume the data like this

A             B           C

12           Null       12

Null         10         10

25            11         14

10            20         10

Do you want this kind of result..?

[EmiliaB_123]

yes

[Angith_Nair]

Try to create calculated column with this code..

Column =
ABS (
    IF (
        ISBLANK ( 'Table'[A] ),
        'Table'[B],
        IF ( ISBLANK ( 'Table'[B] ), 'Table'[A], 'Table'[A] - 'Table'[B] )
    )
)

which will look like this..

[EmiliaB_123]

Hi @Angith_Nair ,

yes this is what I need thanks. I will try this out and mark it as a solution if it works. Thank you very much on the quick reply!