Forum Discussion

KWYew's avatar
KWYew
Frequent Visitor
3 years ago
Solved

Calculating the Difference between Two Categories

Hi all   I have a data table as such: If I want a bar chart showing the difference between any two months (interactively chosen using slicers), sorted from largest to smallest, and being abl...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi KWYew 

     

    Try to feed two slicers with two isolated tables, then you may write a measure like:
    Delta_Value =
    VAR _value1 =
    SUMX(
        FILTER(
            RawData,
            RawData[Month]=SELECTEDVALUE(Slicer1[Month])
        ),
        RawData[Value]
    )
    VAR _value2 =
    SUMX(
        FILTER(
            RawData,
            RawData[Month]=SELECTEDVALUE(Slicer2[Month])    
        ),
        RawData[Value]
    )
    RETURN
    _value1 - _value2
     
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi KWYew 

 

Try to feed two slicers with two isolated tables, then you may write a measure like:
Delta_Value =
VAR _value1 =
SUMX(
    FILTER(
        RawData,
        RawData[Month]=SELECTEDVALUE(Slicer1[Month])
    ),
    RawData[Value]
)
VAR _value2 =
SUMX(
    FILTER(
        RawData,
        RawData[Month]=SELECTEDVALUE(Slicer2[Month])    
    ),
    RawData[Value]
)
RETURN
_value1 - _value2
 
KWYew's avatar
KWYew
Frequent Visitor
3 years ago

Thanks FreemanZ for the idea!
I had to modify the formula slightly for it to work, cause te FILTER() function somehow is not working as intended, probably due to the way my data is structured.

Below is what worked for me (I still had to create a dummy, unrelated table to store the [Month] for Slicer1):

Delta_Value =
VAR _SelectedSlicer = SELECTEDVALUE(RawData[Month])
VAR _SelectedSlicer1 = SELECTEDVALUE(RawDataDummy[Month])
VAR _value1 =
CALCULATE(SUMX('RawData','RawData'[Value]),'RawData'[Month] = _SelectedSlicer)
VAR _value2 =
CALCULATE(SUMX('RawData','RawData'[Value]),'RawData'[Month] = _SelectedSlicer1)
RETURN
_value1 - _value2