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 able to drill up/down for 'Type', 'Group', and 'Item', how should I go about it?

Is any data structure change required?

What I've tried:

1) Creating calculated measures for filtering month1 and month2

Value1 = SUMX('RawData','RawData'[Value])

Value2 = CALCULATE(SUMX('RawData','RawData'[Value]),ALL('RawData'[Month]),USERELATIONSHIP('RawData'[Month],'RawData'Dummy[Month]))

 

Value1 will change according to the selected month1 using Slicer1 (without affecting Value2 since ALL() is used), Value2 will change according to the selected month2 using Slicer2.

'RawData' and 'RawDataDummy' are linked with an inactive relationship.

Slicer1 will use 'RawData'[Month] and Slicer2 will use 'RawDataDummy'[Month]

 

2) Create a calculated measure for the difference between two values

Delta_Value = [Value1] - [Value2]
 
Somehow I'm getting the same value for all rows instead of their respective difference:

 

Can anybody please help?
  • 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
     

2 Replies

  • 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