cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JanBuzek
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
Microsoft
Microsoft

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.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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.

View solution in original post

JanBuzek
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

Microsoft
Microsoft

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

Re: Employee age structure

Thank you so much. I think I get it now:)

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors