topic Re: Calculating the Difference between Two Categories in DAX Commands and Tips

Calculating the Difference between Two Categories

KWYew — Sat, 25 Mar 2023 07:30:41 GMT

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?

Re: Calculating the Difference between Two Categories

FreemanZ — Sat, 25 Mar 2023 09:11:42 GMT

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

Re: Calculating the Difference between Two Categories

KWYew — Wed, 29 Mar 2023 03:53:10 GMT

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