Calculate Notional Cost per User per Day

[Anonymous]

Hi 

I'm trying to calculate a notional cost per user per day. The data has multiple entries per user per day with an hourly rate column. These differ between users and individual days per user, but are consistent for the user across the same day. 

I have the following dax calculation where I am summarising the data by user and day and then adding a day cost (hourly rate x 7.5) and then iterating through this to calculate the total.

TotalCostbyDay = 
VAR CostPerDay =
  averagex(Data, Data[HourlyRate] ) * 7.5
VAR SummarisedTbl =
    Addcolumns(SUMMARIZE ( Data, Data[User], 'Calendar'[Date] ),"CostperDay", CostPerDay)
VAR TotalCostbyDay =
   sumx(SummarisedTbl,  CostPerDay)
RETURN
TotalCostbyDay

The values appear to be calculating correct per user/day but the totals aren't.

Data

Would be grateful if someone could correct the DAX or suggest a better way to do this.

Many thanks

[johnt75 · Accepted solution]

I think the problem is that the CostPerDay variable is only being calculated once, for all dates, not once per day which I think is your intention. Try

TotalCostbyDay =
VAR SummarisedTbl =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[User], 'Calendar'[Date] ),
        "CostperDay", CALCULATE ( AVERAGEX ( Data, Data[HourlyRate] ) ) * 7.5
    )
VAR TotalCostbyDay =
    SUMX ( SummarisedTbl, CostPerDay )
RETURN
    TotalCostbyDay

SQL BI recently did an excellent video on variables which explains, amongst other things, that variables are not really variables, they are constants.

[Anonymous]

Hi Johnt75

Many thanks for reply. I had to amend it slightly by putting a filter within the Calculate but that's corrected the issue. 

TotalCostbyDay = 
VAR SummarisedTbl =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[User], 'Calendar'[Date] ),
        "CostperDay", Calculate(AVERAGEX(Data,Data[HourlyRate])*7.5,Data[HourlyRate]>0))
VAR TotalCostbyDay =
    SUMX ( SummarisedTbl, [CostperDay] )
RETURN
    TotalCostbyDay

Thank you for the video link also.