cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

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-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!

2 REPLIES 2
Super User III
Super User III

Re: Highly Dynamic Per-Capita Event Rate Calculation

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 Member
Member

Re: Highly Dynamic Per-Capita Event Rate Calculation

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors