Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Difference between two columns in matrix visualisation

Hi,   I have in a matrix a row with two columns. Row is company name, and columns are 201806 en 201807 I can make a total (setting a subtotal), but how to make a difference? Example: Company 20...
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous,

     

    Please try to use below formula to replace amount column and drag it to value field. then you will find diff effect apply on total level.

    Diff =
IF (
    ISFILTERED ( Table[Date] ),
    SUM ( Table[Amount] ),
    CALCULATE (
        SUM ( Table[Amount] ),
        VALUES ( Table[Company] ),
        Table[Date] = 201806
    )
        - CALCULATE (
            SUM ( Table[Amount] ),
            VALUES ( Table[Company] ),
            Table[Date] = 201807
        )
)

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Xiaoxin Sheng,

 

Your last formula works, but how to put Diff in a Matrix Visualation instead of a subtotal.

Normally you have this in an Matrix where YearMonth 201806 en 201807 is selected (filter)

 

Company 201806 201807 Subtotal

X              100      200        300

Y              50        100        150

 

Instead of subtotal i want to have column Diff.

When i now select Diff in a Matrix you get this:

 

Compmany   201806 Diff  201807 Diff

X                 100      -100   200    200

 

I want to have this:

Compoany  201806 201807 Diff

X                100       200     100

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI Anonymous,

 

Please try to use below formula to replace amount column and drag it to value field. then you will find diff effect apply on total level.

Diff =
IF (
    ISFILTERED ( Table[Date] ),
    SUM ( Table[Amount] ),
    CALCULATE (
        SUM ( Table[Amount] ),
        VALUES ( Table[Company] ),
        Table[Date] = 201806
    )
        - CALCULATE (
            SUM ( Table[Amount] ),
            VALUES ( Table[Company] ),
            Table[Date] = 201807
        )
)

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This one got closest to what I needed after scouring the interwebs for ages! Thank you very much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    What about if we have more than 2 years like 2015 , 2016 , 2017, 2018, 2019, 2020

    Thank You

     

  • AAspa's avatar
    AAspa
    Frequent Visitor
    1 year ago

    Hello, sorry but this formula will generate 3 columns: one difference under the column 201806, one below 201807, and one under the totals. Now, i think the question here and in many other forums, is to compute dynamically the difference between the columns selected with a slicer and get only one column with the computed difference. Is there a solution for this? many thanks