How to calculate % of students by city that have enrolled, assisted or completed a class.

[Bedski]

Hello to all hope you are doing well today.

I’ve been at it for quite a while now then I decided to as for help.

I have a table named “ClassStats” that has 3 stats: Students, Cities and Status.

I want to calculate the % of students that have either enrolled, not started or completed their classes by city name.

I've checked countless web sites dans YouTube videos and I have this so far that works for me:

TEST1 = CALCULATE(COUNTA(ClassStats[Cities]), FILTER(ClassStats,ClassStats[Cities] = "City 1"))

It returns the number of students in “City 1”.

Now what I would like is something like (sort of like a countif in Excel)

TEST1 = CALCULATE(COUNTA(ClassStats[Cities]), FILTER(ClassStats,ClassStats[Cities] = "City 1" calculate % of students by Status

||(or) ClassStats,ClassStats[Cities] = "City 2" calculate % students by Status

||(or) ClassStats,ClassStats[Cities] = "City 3" calculate % students by Status))

Any help would be greatly appreciated.

Thank you and have a nice day 🙂

[Vera_33]

Hi @Bedski 

What is your expected result? I am not very sure if it is...

% of students by Status = DIVIDE(COUNTROWS('Table'), CALCULATE(COUNTROWS('Table'),ALL('Table'[Status])))

[Bedski]

Hello @Vera_33 ,

Thank you for helping me with my issue.

If you are like me, with a screen capture, it works sometimes better. 😉

Here I have my graph Total of Students x Status and I have a custom tooltip.

On my tooltip, I would like to get two types of percentage:

#1: % of students/city  

#2 overall % (all the cities together).

I already have my city name (City 1 in blue), the status (Enrolled), total of student/city (2 enrolled in city 1) and finally total overall student (10).

The % of students/city (100%) we have here, is your formula and no matter where I move my mouse, it will still show 100%.

Hope that help, thank you again for your time 😊

Bedski

[Vera_33 · Accepted solution]

Hi @Bedski 

You are doing a report page tooltip? If you drag the measures to the Tooltip, are these what you are looking for? not sure what your Tooltip page looks like?

Total = COUNTROWS(ALL('Table'))

overall % = DIVIDE(COUNTROWS('Table'),[Total])

[Bedski]

Hello @Vera_33

You’ve done it!!!! It works!!! Both formula worked exactly the way you wrote them. First one.

% of students by Status = DIVIDE(COUNTROWS('ClassStats'), CALCULATE(COUNTROWS('ClassStats'),ALL('ClassStats'[Status])))

Gave me % of students by Status here 40% (Not sure why it wasn’t working the first time, I deleted everything and restared from scratch.)

I even used in a different mesure

Total City = CALCULATE(COUNTROWS('ClassStats'),ALL('ClassStats'[Status])) to get my total of users by status by city here 5.

And finally, as you can see your last two mesures gave my overall % (all cities) which is in here 20%.

BTW, I am starting Power BI classes new week 😉

Again thank you sooo much and have a nice day. 😊