Reply
dofrancis3
Helper III
Helper III

Calculate percentage based on other column

Dear Team;

Please i'm requesting your support regarding the DAX mesure. 

I would like to calculate the percentage (lethality rate):

1. According to to the Gender

2. According to The gender and the region

dofrancis3_0-1732575544258.png

Thank you

1 ACCEPTED SOLUTION
SGroff
Regular Visitor

Hi,

 

I would declare variables and use the calculate and countrows functions. I'm not sure what your table names are but I imagine your dax should look something like this:

PercentByGender =
VAR TotalFemalesInfected = CALCULATE(
                                             COUNTROWS('TableName'[GENDER]),
                                             'TableName'[Gender]="F"
)
VAR FemalesDie =
                                            CALCULATE(
                                             COUNTROWS('TableName'[GENDER]),
                                             'TableName'[Gender]="F",
                                             'TableName'[Death]="1",
)
 
VAR LethalityRateFemales = DIVIDE(FemalesDie, TotalFemalesInfected,0)
 
RETURN
IF(LethalityRateFemales>0,LethalityRateFeamles, 0)

I put that IF() statement in the return of a lot of my measures that I use in cards because I hate it when the answer is 0% and the card shows "(BLANK)."

You can use similar expressions for the other percentages you want to find.

 

Remember if you click on your measure, then modeling, you can modify the result to return a percentage and how many decimal places you want to show. 

One final caveat, the calculate() function tends to wreak havoc if you want to use this measure for a drill through.

View solution in original post

3 REPLIES 3
dofrancis3
Helper III
Helper III

Dear @SGroff ,

Thank you so much for your support.

But if i have the tables (1srt one OR second one) below how can i calculate the percentage? (Dax)

dofrancis3_0-1732577516995.png

 

dofrancis3_1-1732577906991.png

 

I think you're asking me if you can calculate mortality percentages for different populations, for example, females between the ages of 5 and 14.  Calculate() allows you to filter by more than one field.  See above where I filtered the countrows() expression by gender and mortality.  Simply seperate the different arguments by a comma. 


SGroff
Regular Visitor

Hi,

 

I would declare variables and use the calculate and countrows functions. I'm not sure what your table names are but I imagine your dax should look something like this:

PercentByGender =
VAR TotalFemalesInfected = CALCULATE(
                                             COUNTROWS('TableName'[GENDER]),
                                             'TableName'[Gender]="F"
)
VAR FemalesDie =
                                            CALCULATE(
                                             COUNTROWS('TableName'[GENDER]),
                                             'TableName'[Gender]="F",
                                             'TableName'[Death]="1",
)
 
VAR LethalityRateFemales = DIVIDE(FemalesDie, TotalFemalesInfected,0)
 
RETURN
IF(LethalityRateFemales>0,LethalityRateFeamles, 0)

I put that IF() statement in the return of a lot of my measures that I use in cards because I hate it when the answer is 0% and the card shows "(BLANK)."

You can use similar expressions for the other percentages you want to find.

 

Remember if you click on your measure, then modeling, you can modify the result to return a percentage and how many decimal places you want to show. 

One final caveat, the calculate() function tends to wreak havoc if you want to use this measure for a drill through.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)