Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |