Forum Discussion
Subtraction Formula doesn´t Work
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!
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 ] ) ) + 0The 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.
12 Replies
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..?
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?
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..?
Did you try replacing 'null' values with 0?
Hi lovishsood1 , it was the first thing I tried and it didn´t work.
are you expecting such results?
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 ] ) ) + 0The 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.
