Aggregating measure that depends on SELECTEDVALUE

[Anonymous]

Hi,

I have created a measure called "Tenure Measure" 

Tenure Measure =

IF (

ROUNDUP(SELECTEDVALUE(selected[Year])-MIN(data[First Yr of Service]),0) < 4,

"0-3",

IF (

ROUNDUP(SELECTEDVALUE(selected[Year])-MIN(data[First Yr of Service]),0) > 3 && ROUNDUP(SELECTEDVALUE(selected[Year])-MIN(data[First Yr of Service]),0) < 7,

"4-6",

IF ( ROUNDUP(SELECTEDVALUE(selected[Year])-MIN(data[First Yr of Service]),0) >= 7 && ROUNDUP(SELECTEDVALUE(selected[Year])-MIN(data[First Yr of Service]),0) < 11, "7-10", ">10" )

)

)

It works out the tenure of the employee by calculating the difference between year of hire and selected year from the slicer. And next it categorizes the tenure value into 1 of the 4 ranges: 0-3, 4-6, 6-10, >10

How do I show a bar chart with 4 bars (1 bar for each range) for the number of employees that are in each tenure range?

[amitchandak]

@Anonymous , Please check current Age measure and Age page in the file attached after signature

Refer to my blog, how it has been created

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi Amit, thanks for the fast response as always.

I am able to calculate the Tenure measure, but I am struggling to convert the Tenure measure as a column.

Example:

I can see the tenure for each employee when i list the employee id (similar to the matrix visualization you have provided in current_employee.pbix, which has employee id as the 1st column), but i am interested in converting the "tenure measure" (which is expressed as a range 0-3, 4-6, 7-10, >10) into a bar chart. 

Hoping to get something like this, which I have illustrated in excel. 

Can you advise?

[amitchandak · Accepted solution]

@Anonymous , bucketing on measure. For this you need an independent table.  ANd you have to create an independent table and use that for creating additional measure which can give the count

refer my video - https://www.youtube.com/watch?v=CuczXPj0N-k

[Anonymous]

thank you very much! This works!!!