cancel
Showing results for
Did you mean:
Frequent Visitor

## Subtraction and Percentage difference

Hi everyone.

I have "net shipmant" column from 2019 and "net shipmnat" from 2018.

"variance order" is my 2019 and 2018 filter.

now I want to create 2 columns wich will be calculate

1. Net variance \$   (2019-2018)

2. Net variance %

For the first case the results are :

\$152.50 - \$499.00 =  (\$346.50)

\$0 - \$118.48 = (\$118.48)

\$1,655.88 - \$5,466.85 = (\$3,810.97)

......

my sql code: =Sum(iif(Fields!VarianceOrder.Value=1,val(Fields!NetShipments.value),0))
-
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0))

For the second case the results are:

-69.44%

-100.00%

-69.71%

.....

my sql code: =(Sum(iif(Fields!VarianceOrder.Value=1,val(Fields!NetShipments.value),0))
-
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0)))
/
Sum(iif(Fields!VarianceOrder.Value=2,val(Fields!NetShipments.value),0))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Subtraction and Percentage difference

Hi @Vrto95

You may create two measures to get the value.

```Percentage =
VAR _2019 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2019 )
)
VAR _2018 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2018 )
)
RETURN
IF (
ISFILTERED ( Table1[Year] ),
SUM ( Table1[NetShipments] ),
FORMAT ( DIVIDE ( _2019 - _2018, _2018 ), "Percent" )
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

## Re: Subtraction and Percentage difference

Hi @Vrto95

You may create a measure like below.Attached sample file for your reference.

```Measure =
VAR _2019 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2019 )
)
VAR _2018 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2018 )
)
RETURN
IF (
ISFILTERED ( Table1[Year] ),
SUM ( Table1[NetShipments] ),
IF (
SELECTEDVALUE ( Table2[Order] ) = 1,
_2019 - _2018,
IF (
SELECTEDVALUE ( Table2[Order] ) = 2,
DIVIDE ( _2019 - _2018, _2018 ),
SUM ( Table1[NetShipments] )
)
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Subtraction and Percentage difference

it's not work :/ ... are there any other idea?

Frequent Visitor

## Re: Subtraction and Percentage difference

@v-cherch-msft , I need two columns one is substraction another one is percentage. In your example I understand that with checkbox i can select wiche one i want to  see, but checkbox in my report is just 2018 and 2019 years columns

Highlighted
Community Support Team

## Re: Subtraction and Percentage difference

Hi @Vrto95

You may create two measures to get the value.

```Percentage =
VAR _2019 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2019 )
)
VAR _2018 =
CALCULATE (
SUM ( Table1[NetShipments] ),
FILTER ( Table1, Table1[Year] = 2018 )
)
RETURN
IF (
ISFILTERED ( Table1[Year] ),
SUM ( Table1[NetShipments] ),
FORMAT ( DIVIDE ( _2019 - _2018, _2018 ), "Percent" )
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.