Calculate Rates and Display on Chart

[ahiemstra]

I have a dataset which is structured like this:

I’m able to plot raw referral numbers on a chart and display them by team and by fiscal year. See Page 1 in sample .pbix file linked at the bottom of this post.

Each team is a different size and as such, to truly compare referral volumes across teams, it’s ideal to convert the volumes to rates (rate per 1,000 for example.)

An additional layer is that team sizes fluctuate from year to year. Below are the team sizes over the various fiscal years in my dataset.

I would like to take the volume of referrals for each team for each fiscal year, use the appropriate team size for the calculation, and have the rates plotted on the chart rather than the referrals. For example:

For Team A for 2021/22

Total referrals 96 / team size for 2021 12,865 x 1,000 = 7.5

As a reference, below are tables showing the referral volumes and the resulting rates:

Is it possible to have these rates calculate behind the scenes and the resulting rates plotted on the y-axis rather than the referral volumes?

Sample report: https://ln5.sync.com/dl/02c25e090/7shjyrmk-s6w6etj9-ay8wnch9-hwy4nhjz

[Anonymous]

Hi, @ahiemstra 

The link you shared does not download the pbix file properly, you can use Google Drive to re-upload it, and based on the information you've given, it's not clear at the moment how you got your Team Size table and Referrals table.

Best Regards,
Yang
Community Support Team

[ahiemstra]

I’m not sure why you are not able to download the .pbix file, I’ve tried on a few computers and the link works OK for me. Here is a link via Google in the event this works better for you:

https://drive.google.com/file/d/1TJKtIfnmffLJbQEBPSwUIuXo83TkxsvE/view?usp=sharing

What I’m trying to do here is completely new to me and I don’t truly know if it is possible. Hence the reason I’m hoping someone can advise.

The Team Size table which I included an image of in my original post, isn’t currently in my data model. The reason being, I didn’t know if it needed to be (and if so in what format) or if the team sizes would be hard coded into a DAX of some sort.

The Team Size table in my post was from Excel and its purpose was to illustrate that each team is a different size and what those sizes are from year to year, to help you understand that element of my question.

If this table needs to be added to the model, should the format be like this:

Or like this:

Similarly, the Referrals image capture in my original post is simply showing you a summary of the data found in the REFERRALS table which is in my data model. Again, this was to help illustrate my question. I don’t believe this referral summary table needs to be in my model given I have the raw data.

Lastly, the Rate per 1,000 table shows you the rates I’m hoping to calculate and plot on the y-axis based on the referrals and team sizes.

Hopefully this provides clarity to my question. It may be that what I'm trying to achieve isn't possible and instead, I need to create a table with the rates already calculated and use this as the source for my chart.

[Anonymous]

Hi, @ahiemstra 

I can open the pbix file, but there is no Team size table related data in it, how do I get the Team size table?

Best Regards,
Yang
Community Support Team

[ahiemstra]

Please see my presvious response, I explained how there isn't a Team Size table currently in the data model and I explained why.

You may disregard this topic, I found an alternate solution to my question.

Thank you.