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

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