cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculate rates based on gender and age group selection

Hi Guys,

I’m looking for some help to calculate rates per 100,000 by selected age band(s) and gender.

I have a Gender and an Age Group selection filter in my report.

I also have a Gender table which is linked to my fact table on the Gender field, and the Gender table also contains the populations by each age group, please see the image below. I also have an Age Group field in my fact table. (Please just ignore the Accumulative totals by Week section in the image.)

I have no problem calculating the numerator, that is working fine with any (and all) filter selections by simply using a Countrows function, but my problem is how to get the correct denominator value and to be able to display rates instead of counts in my charts.

If for example, the user selects Male and 30-44 age group I want to be able to calculate:

(Using 150 as a sample numerator): 150 divided by 24,183 multiplied by 100,000, so I would expect a rate of: 620.1.

Similarly the user may select two male age groups, for example: 16-29 and 45-59
(Using 300 as a sample numerator this time) 300 divided by (24,498 + 27,754) multiplied by 100,000, so I would expect a rate of: 574.1.

I have a fact table with many thousands of records and growing, so I am hopefully looking for a DAX measure rather than a calculated column to be able to solve this problem. Besides I’m not quite sure if a calculated column would work if more than one age group was selected.

Thank you, any help would be greatly appreciated, as I am really struggling!

 

Rates by Gender and Age Group.png

 

 

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@SGP1 , I am not able to relate you two numbers. One in bar visual and another one in excel screenshot. Ideally, both should be joined to a common age group table(Dimension Table) , and then it simply the sum of data from one table divide my another table

 

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@SGP1 , I am not able to relate you two numbers. One in bar visual and another one in excel screenshot. Ideally, both should be joined to a common age group table(Dimension Table) , and then it simply the sum of data from one table divide my another table

 

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hello amitchandak and thank you for your reply. Please find a link to my sample pbix file with an anonymised fact table and lookup tables. (I think I might be storing the data incorrectly in my lookup tables? so happy to change these if necessary.) Any further help would be much appreciated. The counts (numerators) are working fine, I just can't seem to figure out how to calculate the correct rates, especially if two or more age groups etc. are selected.

I have also included ethnicity as I will need this calcuating as well in the same fashion. Thank you. ( I can't attach files so hopefully the link below will work, please let me know if not.)

<iframe src="https://onedrive.live.com/embed?cid=51E6A7E21A9322CB&resid=51E6A7E21A9322CB%21535&authkey=AIQgKLQOes..." width="98" height="120" frameborder="0" scrolling="no"></iframe>

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors