Forum Discussion

mdonawho's avatar
mdonawho
Frequent Visitor
3 years ago
Solved

Beginner Help - Want to add variance column to table

I am trying to figure out how to add a variance column (difference between 2021 and 2022 for March would be -20) to a table.  Any help would be appreciated.

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi mdonawho ,

     

    It seems that you are using a Matrix visual?

    If so please try:

    Rename the Column Subtotal:

    Then apply the measure to the value field:

    Measure =
IF (
    ISINSCOPE ( 'Table (2)'[Year] ),
    SUM ( 'Table (2)'[Value] ),
    CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2022 ) )
        - CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2021 ) )
)

    Output:

    Another solution:

    You can Pivot your column:

    Output:

    Then create a measure like negi007  mentioned :

    Varience = SUM('Table'[2022]) - SUM('Table'[2021])

    Final output:

    Best Regards,

    Jianbo Li

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    mdonawho , Create MOYM measure and use that in column total using isinscope

     

    measure = if(isinscope(Table[Year]), [MTD], [MOYM])

     

     

    Measure using time intelligence and date table

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
    last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

     

     

    MOYN =[MTD Sales] - [last year MTD Sales]

     

    • mdonawho's avatar
      mdonawho
      Frequent Visitor
      3 years ago

      Thank you for your reply.  I am not understanging the steps you are directing me to take. 

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    3 years ago

    mdonawho in this case you can create below measures

    2021_value = CALCULATE(SUM(sales[value]),YEAR=2021)

    2022_value = CALCULATE(SUM(sales[value]),YEAR=2022)

    variance = [2022_value]-[2021_value]

     

    you can then use these measures in your matrix to view value for each of the measure

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi mdonawho ,

     

    It seems that you are using a Matrix visual?

    If so please try:

    Rename the Column Subtotal:

    Then apply the measure to the value field:

    Measure =
IF (
    ISINSCOPE ( 'Table (2)'[Year] ),
    SUM ( 'Table (2)'[Value] ),
    CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2022 ) )
        - CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2021 ) )
)

    Output:

    Another solution:

    You can Pivot your column:

    Output:

    Then create a measure like negi007  mentioned :

    Varience = SUM('Table'[2022]) - SUM('Table'[2021])

    Final output:

    Best Regards,

    Jianbo Li

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