Comparaison between old category and new category

[AxelKAp]

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 !

[AxelKAp]

@johnt75 It works ! Thank a lot for your support 🙂

[johnt75 · Accepted solution]

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