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
Anonymous
Not applicable

Calculate sum of distinct values in a data range

Hi,

 

I would like to calculate a FTE (full time equivalence), a measure to calculate the headcount :

 

HC-Headcount = IF (
[Data Date] >= MIN ( 'Calendar'[Date] ),
CALCULATE (
DISTINCTCOUNT ( 'xa BuildUp To Gross'[Payroll nb] ),
FILTER (
'xa BuildUp To Gross',
COUNTROWS (
FILTER (
RELATEDTABLE ( xADM00_General),
'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] )
&& (
ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) 
|| 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] )
)
)
)
> 0
)
)
)

 

I’ve tried to use the same formula of headcount for FTE just changing the line : countrows for sum, sumx, but isnt work. 

 

for the headcount, each one has a FTE (full time equivalence), expressed in the column C.

 

Untitled.png

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've found the solucution : 

 

1. I've create a inative relation between the calendar Date and the reference date in the database

2. and use this measure: 

 

HC - FTE = CALCULATE (
SUM ('xa BuildUp To Gross'[FTE]), FILTER ( 'xa BuildUp To Gross', 'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] ) && ( ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) ) ), USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date]))

 

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

It's tough to tell exactly what you are trying to do here but I am guessing you want the FTE for a point in time taking into considering the start and end dates of employees? Have a look at my new measure in the Quick Measures gallery, made possible by @Phil_Seamark's fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence. The Quick Measure link is here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

 

Should be easily adaptable to your situation.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

HI @Greg_Deckler, Thanks for your qucik reply. 

 

It's almost what I'm looking for, but your measure counts rows, and I'd like to sum the rows, I'tll try to be more clear: 

 

I have a payroll file that I've received every month, with some columns: 

 

- Reference: respective month and year 

- Payroll number : number of register of the employee 

- FTE : Full time equivalence 

- Work pattern: describing if the employee is part time or full time 

- Administrative start date : Hiring date, the date that starts the contract with the company 

- Administrative End date: Leving date, the date that finished the contract with the company 

 

In the powerbi database every month the employees record repeats itself until the month he/she left the company (If active the administrative End date is blank). Example: 

 

REFERENCE      PAYROLL NB         FTE         WORK PATTERN      ADM START DATE       ADM END DATE 

JAN 2017                 001                    0.6                 Part time                    10-01-2017 

JAN 2017                 002                    0.4                 Part time                    20-01-2017 

FEB 2017                  001                   0.6                  Part time                    10-01-2017 

FEB 2017                  002                   0.4                  Part time                    20-01-2017 

FEB 2017                  003                     1                   Full time                     01-02-2017 

MAR 2017                001                     1                   Full time                     10-01-2017 

MAR 2017                002                   0.4                  Part time                     20-01-2017 

MAR 2017                003                    1                    Full time                      01-02-2017 

...

MAR 2018                001                     1                   Full time                     10-01-2017 

MAR 2018                002                   0.4                  Part time                     20-01-2017 

MAR 2018                003                    1                    Full time                      01-02-2017

 

The troube/issue is: 

As headcount each month employee should counted as 1 ( it's sorted with the measure HC - Headcount as i've written above), but as FTE i need to sum the cvalues for FTE tto have the result: 

 

JAN 2017 Headcount is 2 and FTE is 1 

FEB 2017 Headcount is 3 and FTE is 2 

MAR 2017 Headcount is 3 and FTE is 2.4 

MAR 2018 Headcount is 3 and FTE 2.4 

 

As you can see depending on the month the FTE value can change, and i need to sum for each month the respective values. 

 

I have other tables with relationship to this one to use as filter, and a calendar table with no relation with the others. 

Anonymous
Not applicable

@Greg_Deckler

 

I've met a alternative way to make the sum, but I'm struglling to insert the filter for sum actives ones: 

 

FTE = IF (
    [Data Date] >= MIN ( 'Calendar'[Date] ), 
CALCULATE(Sum('xa BuildUp To Gross'[FTE]),USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date])))
Anonymous
Not applicable

I've found the solucution : 

 

1. I've create a inative relation between the calendar Date and the reference date in the database

2. and use this measure: 

 

HC - FTE = CALCULATE (
SUM ('xa BuildUp To Gross'[FTE]), FILTER ( 'xa BuildUp To Gross', 'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] ) && ( ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) ) ), USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date]))

 

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.