Forum Discussion
Calculate difference between multiple values in same column based on week of column
Im trying to create a new column (Price Delta) that calculates the difference between price of the 5 different Brands (R, D, G, C, P) and the "Other Avg" for each week.
Example for week of 1/1/24:
Brand R price (16.6) minus Other Avg price (14.8) = Price Delta (1.8)
Brand D price (14.4) minus Other Avg price (14.8) = Price Delta (-0.4)
etc...
This should done for each week based on that weekly "Other Avg" value
Here is the data set I'm referring to and the desired column in red with desired values:
| Year | Week | Week Of | Brand | Price | Price Delta |
| 2024 | 1 | 1/1/24 | Brand R | 16.6 | 1.8 |
| 2024 | 1 | 1/1/24 | Brand D | 14.4 | -0.4 |
| 2024 | 1 | 1/1/24 | Brand G | 13.4 | -1.4 |
| 2024 | 1 | 1/1/24 | Brand C | 17.5 | 2.7 |
| 2024 | 1 | 1/1/24 | Brand P | 16.6 | 1.8 |
| 2024 | 1 | 1/1/24 | Other Avg | 14.8 | |
| 2024 | 2 | 1/8/24 | Brand R | 17.5 | 3.2 |
| 2024 | 2 | 1/8/24 | Brand D | 13.8 | -0.5 |
| 2024 | 2 | 1/8/24 | Brand G | 13.3 | -1.0 |
| 2024 | 2 | 1/8/24 | Brand C | 17.3 | 3.0 |
| 2024 | 2 | 1/8/24 | Brand P | 16.9 | 2.6 |
| 2024 | 2 | 1/8/24 | Other Avg | 14.3 |
Thanks in advance!
Hi Johnny78968798 ,
You can produce your required output by writing a calculated column like below:
Price Delta Calculated = VAR CurrentWeek = TableName[Week] VAR OtherAvgPrice = CALCULATE( MAX(TableName[Price]), FILTER( TableName, TableName[Week] = CurrentWeek && TableName[Brand] = "Other Avg" ) ) RETURN [Price] - IF( TableName[Brand] <> "Other Avg", OtherAvgPrice, BLANK() )I have attached an example pbix file.
Best regards,
4 Replies
Create a new column to calculate the "Price Delta". You can do this by using the CALCULATE and FILTER functions in DAX to get the "Other Avg" price for each week and then subtract it from the price of each brand.
Here is the DAX formula to create the "Price Delta" column:
Price Delta =
VAR CurrentWeek = 'Table'[Week]
VAR OtherAvgPrice =
CALCULATE(
MAX('Table'[Price]),
FILTER(
'Table',
'Table'[Week] = CurrentWeek && 'Table'[Brand] = "Other Avg"
)Hey bhanu_gautam
You didn't paste the end of the measure (missed the Return and actual calculation):
Price Delta Forum = VAR CurrentWeek = 'Table'[Week] VAR OtherAvgPrice = CALCULATE( MAX('Table'[Price]), FILTER('Table', 'Table'[Week] = CurrentWeek && 'Table'[Brand] = "Other Avg") ) RETURN OtherAvgPrice-'Table'[Price]Cheers,
Hi Johnny78968798 ,
You can produce your required output by writing a calculated column like below:
Price Delta Calculated = VAR CurrentWeek = TableName[Week] VAR OtherAvgPrice = CALCULATE( MAX(TableName[Price]), FILTER( TableName, TableName[Week] = CurrentWeek && TableName[Brand] = "Other Avg" ) ) RETURN [Price] - IF( TableName[Brand] <> "Other Avg", OtherAvgPrice, BLANK() )I have attached an example pbix file.
Best regards,
Worked perfectly! Thank you!
