Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. N...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    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,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous 

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,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous 

      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,