Subtraction Formula

[rainchong7401]

Hi Friends,
Is there a subtraction formula?
I cannot find on the internet or power bi forum. 
Which is quite tough  to achieve the result i wanted.
Kindly assist me guys.

[Anonymous · Accepted solution]

Hi @rainchong7401 ,

Are you the maximum value minus all other values or from top to bottom, the last row of values minus all the values above?

In the first case, create a measure like this:

Subtract Formula = var _max=CALCULATE(MAX('Table'[Number]),ALLSELECTED('Table'))
var _total=_max*2-CALCULATE(SUM('Table'[Number]),ALLSELECTED('Table'))
return IF(ISINSCOPE('Table'[Number]),SUM('Table'[Number]),_total)

In the second case, you need to create an index column to get the value of the last row and subtract the other values.

Add the index column in Power Query.

Then create a measure.

Subtract Formula 2 = var _maxindex=CALCULATE(MAX('Table'[Index]),ALLSELECTED('Table'))
var _last=CALCULATE(SUM('Table'[Number]),FILTER(ALLSELECTED('Table'),[Index]=_maxindex))
return IF(ISINSCOPE('Table'[Number]),SUM('Table'[Number]),_last*2-CALCULATE(SUM('Table'[Number]),ALLSELECTED('Table')))

Best Regards,

Stephen Tao

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

[ribisht17]

How do you get 34?

I got you can add up to 66

Regards,

Ritesh

[ribisht17]

Please give a clear explanation.

Sub is not a big deal Power BI Measure Sum and Subtract Example - SPGuides

Thanks,

Ritesh

[rainchong7401]

Hi Ritesh,
Sum([column]) = 66

What if I want to have subtraction? (Is there a =Sub() formula)
Sub([column]) = 34