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
    8 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 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!