Highly Dynamic Per-Capita Event Rate Calculation

[jengwt]

I have a list of events associated with employess, and I need to be able to dynamically calculate the per-capita rate of events as users selected slicers. In another thread (https://community.powerbi.com/t5/Desktop/Get-DAX-to-Ignore-Existing-Table-Relationships-for-Calculat...), I was able to hammer down a base calculation for per-capita events using a calculated column, but these calculations do not adequately respond to user input via slicers.

These calcultions are for the per-capita event rate of events by age group. The way I figure this would work is:

Sum(total events by age group of employee at time of event) / Average(sum of all employees in that age group at the time of each event.

Some factors:

• Employees may have multiple events, which are of course listed seperately in this list of event details.
• Employees are identified by unique IDs
• Users can filter this calculation by date range, org hierarchy, age, tenure, etc.

Thank you for your help!

[Cmcmahan]

So as mentioned at the end of the last thread, I would update these calculated columns to be measures. Measures re-calculate every time they are displayed/used in whatever the current context is.  Visuals apply context automatically for you in PowerBI.  To make this easier with your data set, I would suggest you do create one calculated column on your table, that indicates the age range band a specific training took place. Call it [Age Band] or whatever naming convention you use.

How are you displaying the data? I'm assuming in a table or matrix visual, but this solution will work for other visuals as well.  Can you show a picture of what you currently have and/or share a .pbix file with the sensitive data redacted? Even a picture of what a desired outcome would look like would be helpful. It will be much easier to determine how the measures should be set up with that information.

[jengwt]

I've been trying to turn this into a set of measures, but how do you tell a measure to calculate how many people were in an age band at the time of an event, for every selected event and org? That's where I'm having my trouble.