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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jteague
Frequent Visitor

Need percentages of string column based on another string column and put them in a line graph

I want percent status (retained) by gender as my y-axis and term as my x-axis.. I would prefer to drop the A (not retained) altogether from the graph. I tried concatenating gender and status and using this measure:

Measure = DIVIDE(COUNTA('retentionF17F23(gendercatx)'[gedret]),CALCULATE(COUNTA('retentionF17F23(gendercatx)'[gedret]),ALLSELECTED('retentionF17F23(gendercatx)'[gedret])))

But it is giving me percent of total for each group. I want x% of females and x% of males were retained per semester on the graph.

 

I would also prefer not to use gedret at all if possible.

 

Sample of the data:

racegenderage_groupdegree_typeStatustermgedret
African-AmericanMale30 and OverCollege TransferAFall 2017Male A
African-AmericanMale30 and OverCareer / TechnicalRetainedFall 2017Male Retained
African-AmericanMale30 and OverCollege TransferRetainedFall 2017Male Retained
African-AmericanFemale30 and OverCareer / TechnicalRetainedFall 2017Female Retained
African-AmericanMale30 and OverCareer / TechnicalRetainedFall 2017Male Retained
African-AmericanMale30 and OverCareer / TechnicalAFall 2017Male A
African-AmericanFemale30 and OverCollege TransferRetainedFall 2017Female Retained
African-AmericanFemale30 and OverCollege TransferRetainedFall 2017Female Retained
African-AmericanMale30 and OverCareer / TechnicalAFall 2017Male A
African-AmericanFemale30 and OverCareer / TechnicalRetainedFall 2017Female Retained
African-AmericanMale30 and OverCareer / TechnicalAFall 2017Male A
WhiteFemale18-24College TransferRetainedFall 2017Female Retained
WhiteFemale30 and OverCollege TransferRetainedFall 2017Female Retained
WhiteFemale18-24College TransferRetainedFall 2017Female Retained
African-AmericanMale30 and OverCareer / TechnicalAFall 2017Male A
WhiteMale30 and OverCareer / TechnicalAFall 2017Male A
African-AmericanFemale30 and OverCareer / TechnicalRetainedFall 2017Female Retained
WhiteFemale30 and OverCareer / TechnicalAFall 2017Female A
1 ACCEPTED SOLUTION

Hi,

These measures work

Members = COUNTROWS(Data)
Retained = CALCULATE([Members],Data[Status]="retained")
Retention rate = DIVIDE([Retained],[Members])

Hope this helps,

Ashish_Mathur_0-1717378734608.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Based on the sample data that you have shared, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for the reply.

 

Using variables gender and  status I would like the result:

Male : retained = 33% A= 67%

Female  :retained= 89% A= 11%

I cannot understand your expected result.  Someone who does will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

gender Status
Male A
Male Retained
Male Retained
Female Retained
Male Retained
Male A
Female Retained
Female Retained
Male A
Female Retained
Male A
Female Retained
Female Retained
Female Retained
Male A
Male A
Female Retained
Female A

 

percentage = DIVIDE(COUNTA('retentionF17F23(2)'[Status]),CALCULATE(COUNTA('retentionF17F23(2)'[Status]),all('retentionF17F23(2)'[Status])))

By using the measure above I can get overall retention rates. I want to further disagregate it by gender as well.

 

In the sample data there are 9 males and 9 females. Of the 9 males 3 were retained =33%

of the 9 females 8 were retained 89%. Hopefully that makes it easier to understand for everyone.

 

Hi,

These measures work

Members = COUNTROWS(Data)
Retained = CALCULATE([Members],Data[Status]="retained")
Retention rate = DIVIDE([Retained],[Members])

Hope this helps,

Ashish_Mathur_0-1717378734608.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Worked perfect. Thank you! Anyone know of any free resources for Power BI training?

jteague
Frequent Visitor

I have been able to get to the chart below using this measure:

percentage = DIVIDE(COUNTA('retentionF17F23(2)'[Status]),CALCULATE(COUNTA('retentionF17F23(2)'[Status]),all('retentionF17F23(2)'[Status])))
thumbnail_IMG_2280.jpg

 But I need it to look like this:

thumbnail_IMG_2281.jpg

 I achieved this by dividing the (retention percentage by gender) by number of students and using sum but then it will not work with slicers.


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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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