cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JanBuzek Frequent Visitor
Frequent Visitor

Employee age structure

 Hello,

 

I am trying to create a model that would predict future age structure of employees. To do this I wanted to have a line chart with Count of employees on Y-axis and different ages on X-axis. Then I would like to use a slicer to "move" the line to the right into the future as the employees would age.

Výstřižek.PNG

I have a table with employees and their age as a data source.

 

I wanted to create a what-if parameter and use this parameter in a custom column calculation but I found that this is not possible as columns are calculated on data load and not continuously according to user interaction.

 

 

Is there another way to accomplish what I am trying to do?

 

Thank you so much for any help.

 

Jan

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Employee age structure

Hi Jan,

 

Please download the solution from the attachment. 

1. Create an independent table [Ages] in case that no all the ages are listed. Don't establish a relationship.

Ages = GENERATESERIES(20, 65, 1)

2. Create a what-if parameter from 0 to the number you need, which means the years. For example, 1 year later.

Measure =
SUMX ( 'Table1', IF ( [Age] = MIN ( Ages[Value] ) - [Parameter Value], 1, 0 ) )

3. Create a visual. 

Employee-age-structure

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Employee age structure

Hi Jan,

 

Please download the solution from the attachment. 

1. Create an independent table [Ages] in case that no all the ages are listed. Don't establish a relationship.

Ages = GENERATESERIES(20, 65, 1)

2. Create a what-if parameter from 0 to the number you need, which means the years. For example, 1 year later.

Measure =
SUMX ( 'Table1', IF ( [Age] = MIN ( Ages[Value] ) - [Parameter Value], 1, 0 ) )

3. Create a visual. 

Employee-age-structure

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JanBuzek Frequent Visitor
Frequent Visitor

Re: Employee age structure

Thank you, that's it!

 

Could you please explain to me how the measure actually works? I cannot seem to wrap my head around it. Specifically the condition:

[Age] = min(Ages[Value]) - [Parameter Value]

 

Why are you searching for the minimum Ages Value and then subtracting the parameter?

 

Thank you. I might be too dumb.

 

Jan

Community Support Team
Community Support Team

Re: Employee age structure

Hi Jan,

 

That's a challenge for me, too. The dates and time are dynamic.

1. The min() function aims to match the ages in the X-axis. Because there isn't a relationship. Actually, it returns a single value. Let's have a look at the example in the last snapshot, it's 27 in the lower visual (the red rectangle). 

2.  The [Parameter Value] is 0 at first. That means we don't add any years. It's simple in this scenario. When the age equals the X-axis value, we tag it 1. Or it's 0. Finally, we sum them up.

3. When we add some years to see the forecast, the X-axis is the ages in the future. So we need to delete the [Parameter Value] to match the actual age in the data table. For example, how many people will be 27 in one year later? We need to count the people who are 26 now. So it's 27 in the X-axis while it's 26 in the data table (the source).

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JanBuzek Frequent Visitor
Frequent Visitor

Re: Employee age structure

Thank you so much. I think I get it nowSmiley Happy