Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
Solved! Go to 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
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
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |