March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I need to calculate the % Enrolled column with a measure. I added some of the data in there so you can see what I need. I know I need to take the sum enrolled for group 1 & location 1/sum enrolled for group 1. However, to write that as a measure is just escaping me. Any help would be appreciated!
Thank you for your help in advance!
Solved! Go to Solution.
PCT Enrolled = DIVIDE ( SUM ( Enrolled[Enrolled] ), CALCULATE ( SUM ( Enrolled[Enrolled] ), ALL ( Enrolled[Location] ) ) )
This specifically shows enrollment across all locations, keeping all other filters in place. It's a bit more limiting than being able to use ALLEXCEPT(), but it should work in your case.
Hope this helps
David
PCT Enrolled = DIVIDE ( SUM ( Enrolled[Enrolled] ), CALCULATE ( SUM ( Enrolled[Enrolled] ), ALLEXCEPT ( Enrolled, Enrolled[Group] ) ) )
Hope this helps
David
Hi David,
Thank you for your assistance. I can't seem to get it to calculate the correct information. For instance, my total enrolled for "Group 1" is 4764 and the enrolled for Location 1 is 321 which should be .067, but the measure is giving me .03233? I'm not sure what I am doing wrong, any ideas?
My code gives you what your example showed. Are you saying that you need the total of Location 1 across all groups divided by Group 1 across all locations?
Sorry to make this confusing, obviously I am new to the whole measure thing.
Here is a screenshot of some of my data:
So, at BYU there are 321 enrolled and the current measure is showing me a % of .03233. However, it should be .0673 as if I divide the enrollment for a location and divide it by the sum of the total enrollment for the first group it would be .0673. I hope that makes sense.
Do you have any other slicers or filters active? What does your model look like? With the basic single table I put together I cannot reproduce your errors.
Ah, yes I should have thought about that sorry. I currently have a year slicer that is active.
If I turn off the year slicer it calculates correctly. How would I allow it to calculate even with a year slicer?
It depends on your data model - how many tables, how they are related, what columns are where, etc.
I will be using the "Year", "District or School", and "Location" tables to run my slicers. Aside from those three items the data tables do not relate to each other. I hope that helps.
PCT Enrolled = DIVIDE ( SUM ( Enrolled[Enrolled] ), CALCULATE ( SUM ( Enrolled[Enrolled] ), ALL ( Enrolled[Location] ) ) )
This specifically shows enrollment across all locations, keeping all other filters in place. It's a bit more limiting than being able to use ALLEXCEPT(), but it should work in your case.
Hope this helps
David
Thank you, I really appreciate your help. It worked perfectly, you are amazing! Thank you again!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
86 | |
73 | |
57 | |
52 |
User | Count |
---|---|
197 | |
133 | |
107 | |
69 | |
65 |