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
Super User

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)