Forum Discussion

hugo_p_23's avatar
hugo_p_23
Helper I
1 year ago

Dax Formula (Average)

I need to calculate the average number of visits per week per consultant taking into account the number of weeks worked by each consultant.

 

I have one table with the information per consultant of the date of the visit, I do a count of rows and I have the number of visits.

 

I have a second table that I have the information of the weeks worked by each consultant, example:

 

 

With this information I built a visualization table with the average number of visits made per week by each consultant, example :

(i have a filter on my report to filter by Year/Weeknum)

 

My problem now is the totalizer of the average visits, with the formula I applied to take into account the number of weeks worked per consultant:

- What i got - 8,46 Visits per week (Dax Formula: Sum([Visits]) / (Max([WeekNum (Until)])-Min([WeekNum (Since)])+1)

 

- What i need - 2,63 (Excel Formula: Average ([Average Visits])

 

The problem is that in the totalizer it will look for the minimum week and the maximum week existing in the table, it does not take into account the number of weeks that each consultant worked.

 

Can anyone help me? 

 

Thanks

Hugo

 

Need Help
Tutorial Requests

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can create a measure like

    Average Visits =
AVERAGEX (
    VALUES ( 'Table'[Consultant] ),
    VAR MinWeek =
        CALCULATE ( MIN ( 'Table'[WeekNum (Since)] ) )
    VAR MaxWeek =
        CALCULATE ( MAX ( 'Table'[WeekNum (Until)] ) )
    VAR NumWeeks = MaxWeek - MinWeek + 1
    VAR Result =
        DIVIDE ( [Sum Visits], NumWeeks )
    RETURN
        Result
)