Difference between two columns in matrix visualisation

[Anonymous]

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 201806  201807 Diff

X              100        50         50

Y              50          50         0

Z              200        100       100

Thnxs

Lex

[kguy]

Is there a way to do this dynamically with filtered columns? The column total dynanmically sums to shown columns, I'd like to do the same with the difference between two or more shown columns.

[Anonymous]

I have 4 columns in my visualization.

1. Value of X

2. % of X [value of X/sum of column total]

3. Value of Y

4. % of Y [value of Y/Sum of column total]

Both X & Y are from different table.

My goal is to calculate the difference between % of X & % of Y in % POINTS.

Has anyone been able to figure it out yet?

Thanks in advance.

[Anonymous]

Have you got the solution for this as I have the same problem in matrix view?

[fletchuk]

I created new measures for each of the columns, one which summed together this year's sales, one which summed together last year's sales and a third measure which calcuated the difference between the two. I then dropped all three of my new measures into the matrix table.

Not sure if this is the most efficient way of doing this but it worked exactly as needed. The important thing appears to be creating new measures rather than new columns.

[fletchuk]

Hi,

Did you ever find a solution to this? I have exactly the same problem and its driving me insane! I just want to calculate the differences between two columns in a matrix but the solutuon escapes me! It should be easy but everything I've tried - including the soluton you were given - puts a "Diff" column after each of the two existing columns. It's not even calculating properly, it shows the first Diff column as all negatives and the second Diff column as all positives. 

[Anonymous]

Same problem here. Any updates?!

[v-shex-msft]

Hi @Anonymous,

You can write a measure to calculate difference between two columns:

Diff =
CALCULATE (
    SUM ( Table[201806] ) - SUM ( Table[201807] ),
    VALUES ( Table[Company] )
)

BTW, if them are form same column you can try to use following measure:

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

Regards,

Xiaoxin Sheng

[Anonymous]

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

[v-shex-msft · Accepted solution]

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]

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

Thank You

[skilledzija]

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