Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
ksheth
Frequent Visitor

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

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

ksheth_0-1742853563471.png

 

Table: Total Hospitalizations

ksheth_1-1742854044448.png

 

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.

 

ksheth_2-1742854406827.png

 

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?

 

1 ACCEPTED SOLUTION
v-pnaroju-msft
Community Support
Community Support

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:

vpnarojumsft_0-1752077602604.png

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.

View solution in original post

11 REPLIES 11
v-pnaroju-msft
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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:

vpnarojumsft_0-1752077602604.png

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
Not applicable

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

vshamiliv_1-1744197207128.png

 

Thank you.

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

 

ksheth_0-1744642129146.png

 

Anonymous
Not applicable

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
Not applicable

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.

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

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

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.

ksheth_0-1742914425608.png

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors