Calculate difference between multiple values in same column based on week of column

[Johnny78968798]

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!

[DataNinja777 · Accepted solution]

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,

[Johnny78968798]

Worked perfectly! Thank you!

[bhanu_gautam]

@Johnny78968798 , 

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"
)

[MNedix]

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,