cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Calculate Notional Cost per User per Day

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

1 ACCEPTED SOLUTION
Super User

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 =
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.

2 REPLIES 2
Super User

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 =
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.

Helper I

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 =
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.