topic Re: Comparaison between old category and new category in DAX Commands and Tips

Comparaison between old category and new category

AxelKAp — Mon, 30 Jan 2023 13:08:14 GMT

Hello everyone,

I have a table with the actual category and another column with new category and I would like to calculate the spend difference between the old and the new category.

Let me explain you with an example:

I would like to have the spend in € for each categories for old and new, like that I can compare if we have less or more spend for each category by [New Spend]- [Old Spend].

Thanks for your support !

Re: Comparaison between old category and new category

johnt75 — Mon, 30 Jan 2023 13:39:59 GMT

Create a category dimension table like

Category = DISTINCT ( UNION ( SELECTCOLUMNS ( DISTINCT ( 'Table'[Old] ), "Category", 'Table'[Old] ), SELECTCOLUMNS ( DISTINCT ( 'Table'[New] ), "Category", 'Table'[New] ) ) )

and then link this table to the data table for both Old and New columns. Only 1 relationship can be active but thats OK.

You can then create a measure like

Diff old and new = VAR OldValue = CALCULATE ( SUM ( 'Table'[Spend] ), USERELATIONSHIP ( 'Table'[Old], 'Category'[Category] ) ) VAR NewValue = CALCULATE ( SUM ( 'Table'[Spend] ), USERELATIONSHIP ( 'Table'[New], 'Category'[Category] ) ) RETURN NewValue - OldValue

and put this in a visual with the category column from the dimension table

Re: Comparaison between old category and new category

AxelKAp — Mon, 30 Jan 2023 14:14:44 GMT

johnt75 It works ! Thank a lot for your support 🙂