Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
CORB
Helper I
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

CORB_0-1659624619473.png

 

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

 

Many thanks

 

1 ACCEPTED SOLUTION
johnt75
Super User
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 =
    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.

View solution in original post

2 REPLIES 2
johnt75
Super User
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 =
    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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.