Forum Discussion

minimynt's avatar
minimynt
Frequent Visitor
10 years ago

Sum repeating rows as one (DAX)

Hi, I'm new to Power BI and I want to ask about the DAX formula.

 

So what I want to do is create bucketing for YearOfService, for example

0-2Years -> 100employees

3-5Years -> 30employees

etc..

 

But the problem is that 1 employee id may has more than 1 YearOfService (YOS) because he may move from 1 department to other department, for example

employee id 101 -> 4 years in department A and 1 years in department B

 

It should've give me 5 years in total for employee id 101 instead of splitting 101 to 4 years and 1 years

 

I have this formula:

Temp YOS to calculate YOS for each employee id,

Total YOS to calculate total YOS for each employee id; like above example, 4yrs + 1yrs = 5yrs,

YOS bucket to create the bucket

 

Temp YOS = IF(ISBLANK('Job History'[End Date]), 
DATEDIFF(RELATED(Employee[Hire Date]), TODAY(), YEAR), 
DATEDIFF('Job History'[Start Date], 'Job History'[End Date], YEAR))
Total YOS = CALCULATE(SUM('Job History'[Temp YOS]), 
FILTER(Employee,Employee[Employee ID]))
YOS Bucket = IF([Total YOS]>=0 && [Total YOS]<=2, "0-2 Years", 
IF([Total YOS]>2 && [Total YOS]<6, "3-5 Years", 
IF([Total YOS]>5 && [Total YOS]<9, "6-8 Years", "8+ Years")))

 

And here's what I got

 

 

 

 

 

 

 

for employee ID 101, I got 2 YOS bucket 3-5Yrs and 8+Years.

 

I want it to only show 8+Yrs since it's the total YOS for that employee ID.

 

Thanks.

Priscilla

 

Need Help

3 Replies

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    Are you calculating these as columns in a table? if so you should really try to calculate this as measures instead.

    • minimynt's avatar
      minimynt
      Frequent Visitor
      10 years ago

      Hi sdjensen, Eric_Zhang I can't use measure because actually I also want to create a bar chart like this

      If I create it using measure, it can only be put in VALUE field instead of AXIS.

       

      Also, when I create using measure, it only works well if I select 1 employeeID.

       

      It shows both as 13+ Years instead of 9-11 & 13+ (what I want)

       

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    minimynt

     

    It looks like you are looking for a DAX as

    total Bucket = LASTNOBLANK([YOS Bucket])

    Please be more specific on your scenario. Post some data and elaborate where do you apply your formulas, are they measures or calculated columns?