Help w/ALLSELECTED when using Slicers

[stbrooks13]

I'm in campus recruiting and we have divided our target schools into three levels (National, Regional, Local).

I'm creating scores for our target schools based on how they compare to the school level's average.

I'm able to create the formulas in a table view using ALLSELECTED(Schools(School)) in my calculations. But, as soon as a single school is selected, the formulas all show 100%.

How do I keep the School(School Level) in the formulas? Below is an example of one of the school level formulas that I'm comparing the school's actual to:

School Level Sign-On Bonus Average = CALCULATE(SUM(Activity[Sign-On Bonuses]),ALLSELECTED(School[School]))/CALCULATE(DISTINCTCOUNT(School[School]),ALLSELECTED(School[School]))

 

[akice]

Need to know model design and look of desired output to help. Not enough info provided to suggest correct measures. Thanks.

 

[stbrooks13]

Sure, see here: https://drive.google.com/file/d/1CQ2JG39JD4UKWY6I4ofw0861L6Jyb_JV/view?usp=sharing

[FONT=&quot]The drop down only impacts the Score Totals on the right. The true Score Totals figures are in the center.[/FONT][FONT=&quot]
[/FONT]
[FONT=&quot]When USC is selected, a score of 52.38 appears, when I want it to show 0.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The scores shown when USC is selected is the maximum amount available for those categories, but they should show 0.[/FONT]

 

[gazpage]

ALLSELECTED means all items that are included in filters and slicers. If you select a single school the you will get results for the data as if that were the only school in your data. Not that surprised you get100%.

Are you sure you don’t want ALL?

 

[stbrooks13]

Thank you. No I don't want ALL because then the school would be being compared to all schools. I want the school to be compared to the schools only in its level (National, Regional or Local).

 

[stbrooks13]

Do you think using ALLEXCEPT(School,School[School Level]) in place of ALLSELECTED(School,School[School]) would work?

 

[gazpage]

Filter (
ALL ( School ),
School [School Level] = EARLIER ( School [School Level] )
)

Instead of ALLSELECTED.