Forum Discussion
Solved
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...
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,
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!