Dax calculated column for holding max value grouped by date

Question

I have a table that holds ten days of values broken out by hour:

date         hour  sales
11/20/2019   1      10
11/20/2019   2      20
11/20/2019   3      30
...
11/20/2019   23     230

this pattern is repeated for each date until the current date where a row is inserted on the hour with latest sales

I would like to take the last sales amount for each date and divide the prior rows by that value using DAX. I am first trying to create a calculated column to just hold the max sales value, but for some reason I am getting the max value for the table instead of the group:

Estimated[SalesPct] =
var maxSales = MAXX('Estimated'[Exp_Sales])
return
CALCULATE(divide('Estimated'[Exp_Sales], maxSales)

Prakash Gautam Prakash Gautam · Accepted Answer · 2019-11-24T10:22:27

For calculated column following DAX will do:

Estimated[SalesPct] =
VAR maxSales =
    CALCULATE (
        MAX ( 'Estimated'[Exp_Sales] ),
        ALLEXCEPT ( 'Estimated', 'Estimated'[DateColumn] ) //change to your date column 
    )
RETURN
    DIVIDE ( 'Estimated'[Exp_Sales], maxSales )

Thank

Dax calculated column for holding max value grouped by date

1 Answers