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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
acg
Resolver I
Resolver I

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

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])
)
 
 
1 ACCEPTED SOLUTION
themistoklis
Community Champion
Community Champion

@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 )

View solution in original post

3 REPLIES 3
acg
Resolver I
Resolver I

@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

themistoklis
Community Champion
Community Champion

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

themistoklis
Community Champion
Community Champion

@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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors