Your [% Above 80] measure is an aggregation based on the [Above 80] field.
This [Above 80] field is calculated for every record in the data set. You are returning 1 for values above 80 and 0 for values not above 80. [% Above 80] takes the sum(Above 80) / count(Above 80).
Every record gets a 1 for this count field.
Thus, every score is truly "part" of the underlying data in this aggregation. a score 0f 70 contributes a 0 / 1 to the aggregate, and a score of 90 contributes a 1 / 1...
One way you could get around this is to create a parameter that lets the user choose what score threshhold to view. You could base your [Above X] and [% Above X] measures on the parameter entered. Then, you could filter the second view such that [Above X] must = 1.
Hope this helps.
Thanks for your response, Mark.
So it's the COUNT function that's getting me in trouble?
Would it make a difference if the [Above 80] field said:
IF [Score] >= 80
and skip the "ELSE 0"?
If not, does it seem like I could get around this with a table calculation?
Thanks for your help!
It's not so much what it's returning, i.e., 0 or NULL, it's that your [Above 80] field does not act as a filter by virtue of simply existing. You'd have to somehow choose "only the 1's" to in fact filter the second view.
I'm not sure there's a "hidden" way to do this, since you're representing the sum of the result of your IF statement all at once (thus blending 0's/NULL's and 1's and losing the ability to split between them)--in fact, you certainly would NOT want to filter to show only the 1's on the primary tab. If that were the case, you'd see 100% all the time, because it'd ONLY show those who score 80% or higher...
My suggestion of the parameter to select the 80% would also make it possible to make the measure on second tab conditional...
I went ahead and attached the workbook showing what I'm describing.
Note the new field shown with the filters--the parameter called "Show Scores Above."
Action Issue Post.twbx.zip 217.3 KB