topic Re: Convert Calculation from Count of Total into Average Number of Total? in DAX Commands and Tips

Convert Calculation from Count of Total into Average Number of Total?

acg — Sat, 02 Oct 2021 02:09:59 GMT

Hi how can I convert this Calculation from Count of Employees into an Average Number of Employees calculation?

THe calculation needs to find out the average amount of employess that have worked more than 5 hrs over their standard time, indicated by: (rep_timesheet_OVT[OT 5HRS PlUS] =1 ).

I tired a few variations, but where I am not sure is :

how to deal with the distinctcount, which is required to calculate the proper number of total employees.

CNT OT > 5 HRS PERCENT =

DIVIDE(

CALCULATE (

DISTINCTCOUNT( rep_timesheet_OVT[Employee ID]),

FILTER ( rep_timesheet_OVT, rep_timesheet_OVT[OT 5HRS PlUS] =1 )

),DISTINCTCOUNT(rep_timesheet_OVT[Employee ID])

)

Re: Convert Calculation from Count of Total into Average Number of Total?

themistoklis — Sat, 02 Oct 2021 14:37:51 GMT

acg

Try the following formula:

% Share = VAR Volume = CALCULATE ( DISTINCTCOUNT( rep_timesheet_OVT[Employee ID]),FILTER ( rep_timesheet_OVT, rep_timesheet_OVT[OT 5HRS PlUS] =1 )) VAR AllVolume = CALCULATE ( DISTINCTCOUNT(rep_timesheet_OVT[Employee ID]), ALL('rep_timesheet_OVT' ) ) RETURN DIVIDE ( Volume, AllVolume )

Re: Convert Calculation from Count of Total into Average Number of Total?

acg — Sun, 03 Oct 2021 05:17:13 GMT

themistoklis

Brilliant, thank you!

I have one question, what is exactly the difference

between:

ALL =

CALCULATE ( DISTINCTCOUNT(rep_timesheet_OVT[Employee ID]), ALL('rep_timesheet_OVT' ) )

and

ALL = CALCULATE ( DISTINCTCOUNT( rep_timesheet_OVT[Employee ID]))

I don't quite get what the , ALL('rep_timesheet_OVT' at the end does?

Thank you

Re: Convert Calculation from Count of Total into Average Number of Total?

themistoklis — Sun, 03 Oct 2021 11:36:23 GMT

Hello acg

If you use ALL in a CALCULATE function you simply say to the formula to do calculations on all the rows in the table ignoring any filters.

This means that if you have a table with 3 employee types; A, B and C then this formula will return the sum of all employees across all categories