Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 🙂
Solved! Go to 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])
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])))
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
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])
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. 😊
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.