Forum Discussion
Highly Dynamic Per-Capita Event Rate Calculation
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-Calculated/m-p/738881#M356296), 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!
2 Replies
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.
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.
