Forum Discussion

Fara-15's avatar
Fara-15
New Member
2 years ago
Solved

Variance between two columns in Matrix appears twice

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:

AccountForecast JanVarianceForecast FebVariance
A4262
B5-41-4

 

This is what I want:

AccountForecast JanForecast FebVariance
A462
B51-4
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

2 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.