How to create a measure that calculates rates using 2 separate tables

[ksheth]

Hello,

To preface, I am VERY new to PowerBI so as much detail you can give to help solve this issue would be very much appreciated.

I am currently developing a dashboard that shows counts of incidents (let's say, hospitalizations for eating too many Snickers candy bars) across several years, with separate visualizations for incident counts by date, sex, age, and race/ethnicity. I also have interactive filters that affect these visuals (e.g., dropdown slicer where you can select a specific year, which will affect all the other demographic visuals).

All visualizations currently show counts. I would like to add visualizations that show rates of Snickers-related hospitalizations per 10,000 total hospitalizations. I have a table that has counts of total hospitalizations by date for each demographic category of interest. See example data tables below.

Table: Snickers Hospitalizations

Table: Total Hospitalizations

I am struggling with how to create a measure that will allow me to display rates for a specific demographic category that is also interactive with the filters selected. For example, if I try to show rates by Sex, these are the DAX formulas I wrote:

Rate_Male = DIVIDE(COUNTROWS('Snickers Hospitalizations'),SUM('Total Hospitalizations'[Male]))*10000

Rate_Female = DIVIDE(COUNTROWS('Snickers Hospitalizations'),SUM('Total Hospitalizations'[Female]))*10000

However, these measures do not apply only to the specific category in the visualization (e.g., the Rate_Male measure is also being applied to Females, and vice-versa). See snippet below.

What I want is for "Male" to only show the value of "Rate_Male" and for "Female" to only show the value of "Rate_Female", while being interactive to the selected filters. I would also like to do similar such visualizations for the categories of Age Group and Race/Ethnicity. How can I make this happen?

[v-pnaroju-msft]

Hi ksheth,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution, as this helps the broader community.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi ksheth,

We are following up to see if the information we provided was helpful in resolving your issue. If you have any additional questions, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi ksheth,

We are checking in to know if the information we provided helped in resolving your issue.
If you have any further questions, please do not hesitate to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @ pankajnamekar25, for your response.

Hi ksheth,

Based on my understanding of the issue, please find the attached screenshot and sample pbix file for your reference:

We hope the information given helps to solve the issue. If this does not meet your needs, please provide sample data that clearly shows your problem or question in a usable format (not just a screenshot). Make sure the data does not include any sensitive or unrelated information. Also, please mention the expected result based on the sample data you provide.You can follow this link to learn how to upload sample data:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
If you have any more questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[Anonymous]

Hi @ksheth 
you can attach sample file  in the forum as shown below:

Thank you.

[ksheth]

HI @Anonymous, that is not showing up as an option for me - see snippet below.

[Anonymous]

Hi @ksheth 

May I ask if you have resolved this issue? If so, please share helpful insights and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @ksheth 
Thank you for reaching out microsoft fabric community forum.
could you please provide sample pbix to address the query more efficiently.
Thank you.

[ksheth]

Hi @Anonymous ,

Thank you for reaching back out about this issue. I have not been able to resolve it. Unfortunately, my organization does not allow me to share files via OneDrive, Google Drive, DropBox, etc. so I am having difficulty sharing the sample pbix file. I'm not sure if there is another way to do so? I don't see an option to just attach the file to this post.

[pankajnamekar25]

You can use below measure

Rate_Male =
DIVIDE(
CALCULATE(COUNTROWS('Snickers Hospitalizations'), 'Snickers Hospitalizations'[Sex] = "Male"),
CALCULATE(SUM('Total Hospitalizations'[Male]), 'Total Hospitalizations'[Sex] = "Male")
) * 10000

Rate_Female =
DIVIDE(
CALCULATE(COUNTROWS('Snickers Hospitalizations'), 'Snickers Hospitalizations'[Sex] = "Female"),
CALCULATE(SUM('Total Hospitalizations'[Female]), 'Total Hospitalizations'[Sex] = "Female")
) * 10000

[ksheth]

Hi Pankaj,

Thank you for the suggestion. 

My Total Hospitalizations table does not have a column named "Sex", but I tried modifying the code you provided a little bit:

Rate_Male = DIVIDE(CALCULATE(COUNTROWS('Snickers Hospitalizations'), 'Snickers Hospitalizations'[Sex] = "Male"),SUM('Total Hospitalizations'[Male])
) * 10000

Rate_Female = DIVIDE(CALCULATE(COUNTROWS('Snickers Hospitalizations'), 'Snickers Hospitalizations'[Sex] = "Female"),SUM('Total Hospitalizations'[Female])
) * 10000

However, I am still running into the same issue where both Rate_Male and Rate_Female are showing up in both Sex categories, instead of only Rate_Male for males and Rate_Female for females.