I have a requirement when I display race at the state level to display all races and % of students for the race. If the filter is other than the state level, I am supposed to display the top 1 race and the %.
I have all the calculations working except when I try to return the race value.
Calculations
Determine # and % of Students and race for the Top race
top1race = TopN (1,Values( StudentCourseDetails[Race]),StudentCourseDetails[# Distinct Students])
top1racevalue = CALCULATE([# Distinct Students], TOPN(1, VALUES('StudentCourseDetails'[Race]), [# Distinct Students], DESC))
% Top1Race = DIVIDE([Top1RaceValue] , [# Distinct Students])
Determine which % and value to use on visualization
Race% = if (IScrossFILTERED(DistrictSchoolSummary[CountyName]), StudentCourseDetails[% Top1Race],StudentCourseDetails[% of Distinct Students])
**RaceCalcValue = if (IScrossFILTERED(DistrictSchoolSummary[CountyName]), StudentCourseDetails[Top1Race],value(StudentCourseDetails[Race]))
** Error
A single value for column 'Race' in table 'StudentsCourseDetails' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specificing an aggregation such a min, max, count, or sum to get a single result.
And ideas on how to get the correct race values for each condition? If this can be done with a calculation, is there a way to do like a conditional block which would display the correct visualization base on the condition?
Thanks!
------------------------------
Jo Marie Sellner
------------------------------