Calculate rates based on gender and age group selection

[SGP1]

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!

[amitchandak · Accepted solution]

@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.

[SGP1]

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>