Forum Discussion

Johnny78968798's avatar
Johnny78968798
Frequent Visitor
1 year ago
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...
Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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