sumif equivelant

Anything related to PowerPivot and DAX Formuale
qwerty
Posts: 2
Joined: Sat Apr 29, 2017 12:34 pm

sumif equivelant

Postby qwerty » Mon Jun 05, 2017 8:47 pm

This is probably quite simple but i just cant get my head around it.
I have 2 tables one with annual leave balances defined by employee id and another with hours paid defined by employee id and type of hours which could be annual leave or sick or overtime or normal. I need to calculate the total number of annual leave hours taken by each employee and minus it from the balance.
Would use sumif in excel then minus difference but cant work out the equivalent in powerpivot.
Im fairly new to this and been doing a lot of reading but cant quite work it out.

PhilC
Posts: 133
Joined: Tue Sep 09, 2014 8:13 am

Re: sumif equivelant

Postby PhilC » Wed Jun 07, 2017 8:45 am

Hi,

Providing a file with tables, columns and data would help with giving assistance, but the following may get you going in the right direction.

Assumptions:
Tables Names: Balances, Leave_Taken

In Leave_Taken table
Leave Taken:= SUM(Leave_Taken[Hours])
Annual Leave Taken:= CALCULATE([Leave Taken],Leave_Taken[Type]="Annual")
Sick Leave Taken:= CALCULATE([Leave Taken],Leave_Taken[Type]="Sick")

In Balances
Leave Balance:= SUM(Balances[Hours])
Leave Remaining:=[Leave Balance]-[Annual Leave Taken]

You may need to join the two tables based on Employee ID (ie create a list of distinct Employee IDs from both tables)


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests