Forum Discussion

Ninna's avatar
Ninna
Frequent Visitor
3 years ago
Solved

Calculating difference between columns in matrix view based on filter

Hi,   let's say I have matrix table visual with months in rows and countries in columns, and in filter I choose which countries I want to compare. I need to get column (red in picture) that will c...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Ninna 
    You can activate and rename the total column then place the following measure in the matrix instead of the original measure

    ResultMeasure =
VAR NormalValue = [OriginalMeasure]
VAR SelectedCountries =
    ALLSELECTED ( 'Table'[Country] )
VAR Country1 =
    MAXX ( SelectedCountries, 'Table'[Country] )
VAR Country2 =
    MINX ( SelectedCountries, 'Table'[Country] )
VAR Country1Value =
    CALCULATE ( [OriginalMeasure], 'Table'[Country] = Country1 )
VAR Country2Value =
    CALCULATE ( [OriginalMeasure], 'Table'[Country] = Country2 )
VAR Difference = Country2Value - Country1Value
RETURN
    IF ( HASONEVALUE ( 'Table'[Country] ), NormalValue, Difference )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Ninna 
You can activate and rename the total column then place the following measure in the matrix instead of the original measure

ResultMeasure =
VAR NormalValue = [OriginalMeasure]
VAR SelectedCountries =
    ALLSELECTED ( 'Table'[Country] )
VAR Country1 =
    MAXX ( SelectedCountries, 'Table'[Country] )
VAR Country2 =
    MINX ( SelectedCountries, 'Table'[Country] )
VAR Country1Value =
    CALCULATE ( [OriginalMeasure], 'Table'[Country] = Country1 )
VAR Country2Value =
    CALCULATE ( [OriginalMeasure], 'Table'[Country] = Country2 )
VAR Difference = Country2Value - Country1Value
RETURN
    IF ( HASONEVALUE ( 'Table'[Country] ), NormalValue, Difference )
Ninna's avatar
Ninna
Frequent Visitor
3 years ago

Great, that is it. Thanks 🙂