Variance between two columns in Matrix appears twice

[Fara-15]

Hello, 

I have a matrix in which I am comparing the forecast versions. However, the variance repeats after each version. What is the workaround to show the delta in one column? Appreciate your help very much. 

This is what I have:

Account	Forecast Jan	Variance	Forecast Feb	Variance
A	4	2	6	2
B	5	-4	1	-4

This is what I want:

Account	Forecast Jan	Forecast Feb	Variance
A	4	6	2
B	5	1	-4

[Anonymous · Accepted solution]

Hi @Fara-15 

You can refer to the following solution.

Sample data 

Sample measure

Sum = SUM('Table'[Value]) 

1.Create a table

2.Then create a measure

MEASURE =
VAR _a =
    CALCULATE ( [Sum], 'Table'[Month] IN VALUES ( 'Table (2)'[Type] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table (2)'[Type] ) <> "Var",
        _a,
        VAR _b =
            MAXX (
                FILTER (
                    ALLSELECTED ( 'Table (2)' ),
                    [Index]
                        = ( MAX ( 'Table (2)'[Index] ) - 1 )
                ),
                [Type]
            )
        VAR _c =
            CALCULATE ( [Sum], 'Table'[Month] = _b )
        VAR _d =
            MAXX (
                FILTER ( ALLSELECTED ( 'Table (2)' ), [Index] = MAX ( 'Table (2)'[Index] ) - 2 ),
                [Type]
            )
        VAR _e =
            CALCULATE ( [Sum], 'Table'[Month] = _d )
        RETURN
            _c - _e
    )

3.Put the following field to matrix

Output

Best Regards!

Yolo Zhu

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

[Idrissshatila]

@Fara-15 ,

this is normal because the values in which you're adding it appearing under the column in which month name, so how the matrix work is that for every value in the column it will show the measure values.