Create a measure to determine the % enrolled based on a group and location

[bbarth]

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!

[dedelman_clng]

PCT Enrolled =
DIVIDE (
    SUM ( Enrolled[Enrolled] ),
    CALCULATE (
        SUM ( Enrolled[Enrolled] ),
        ALLEXCEPT ( Enrolled, Enrolled[Group] )
    )
)

Hope this helps

David

[bbarth]

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?

[dedelman_clng]

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?

[bbarth]

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.

[dedelman_clng]

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.

[bbarth]

Ah, yes I should have thought about that sorry. I currently have a year slicer that is active.

[bbarth]

If I turn off the year slicer it calculates correctly. How would I allow it to calculate even with a year slicer?

[dedelman_clng]

It depends on your data model - how many tables, how they are related, what columns are where, etc.

[bbarth]

[bbarth]

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.

[dedelman_clng · Accepted 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 

[bbarth]

Thank you, I really appreciate your help. It worked perfectly, you are amazing! Thank you again!