How to normalize Min/Max measures by number of team members

[Alicia_Anderson]

I have a fact table which includes all details of a Team's velocity (points done per Sprint).   I created a matrix to aggregate the results by Program Increment/Iteration.   Sample of the matrix looks like this:    

Then I created a Min Measure to show the Min value per Team.

Note:  Not all values are shown in example above.  The measure works fine and displays what I initially wanted to see. 

Ex: Min for Apothecary is 8; min for Cluster is 23.

Problem:   I need to be able to normalize the results based on Team size.    I have a separate table that shows team size (just 2 columns: Team & Size).

I tried to create a measure to Divide "Min Effort by Iteration" by Team Size but it wants an aggregate.    How can I do that?

I tried this but it is not dividing by the individual team size; it is summing all obviously.   Just not sure how to get it to do what I want. 

Min Size = DIVIDE([Min Effort per Iteration], Sum('Team Size'[Size]))

[v-kkf-msft · Accepted solution]

Hi @Alicia_Anderson ,

Do you create a relationship between column Team in Team Velocity table and column Team in Team Size? This is the result I got using your formula, and it works.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alicia_Anderson · Accepted solution]

I had used the Team field from the Team Velocity table in the visualization.  When I replaced it with the Team field from the Team Size table, it calculated correctly.   I can't say I understand why it gives different results, but I am happy I am able to get the correct value to display.  Thank you. 

[v-kkf-msft · Accepted solution]

Hi @Alicia_Anderson ,

Do you create a relationship between column Team in Team Velocity table and column Team in Team Size? This is the result I got using your formula, and it works.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.