5 Replies Latest reply on Mar 28, 2018 11:18 AM by LaTonya Harrington

Counting the number of scores above and below selected score

Hello,

I would like to create a calculation that counts the number of hospitals that have a score higher than, equal to and less than the selected hospital score.  Example: I select hospital A, the score is 50.  How many hospitals have a score higher than, equal to and lower 50?

"There are ____ hospitals with a median time higher than your hospital"

"There are ____ hospitals with a median time equal to your hospital"

"There are ____ hospitals with a median time lower than your hospital"

• 1. Re: Counting the number of scores above and below selected score

See attached.

I did a FIXED LOD to place the value of your hospital on all rows.  I added it to text.

But notice in your original sheet that the value comes up as 302 and not 247.  That's because a FIXED LOD doesn't look at what's happening in your filters. Your sheet is selecting only ED1.  GO to copy 2 if that sheet.  Here I placed the filter "in context".  That tells Tableau to use the filter when evaluating the FIXED LOD.  And there it is.  we get 247.  That value is on every row.  You can use that value to calc out "Above" "Equal" and "Below".

1 of 1 people found this helpful
• 2. Re: Counting the number of scores above and below selected score

Thank you for the quick response. Will you please help me with the calculations?  I receive an error when attempting the above, equal and below calculations.

Thanks

• 3. Re: Counting the number of scores above and below selected score

In sheet All Hospital (3) I added some calcs.

[Hospital score] is the logical equivalent to SUM(Score) on your sheet.  But by being a FIXED LOD I can make that calc a dimension.  For each hospital we have a total score.

[Above?] is a calc that looks at each hospital and sets a value of 1 if that hospital's score is above the blue bar.  (Otherwise the value is null.)  I displayed that on the Label above the bars.

[Total Above] adds up all the 1 values that got set in [Above?].  I displayed that in the title of the sheet.

See if you can replicate these for [Equal] and [Below].

For the record, the series of calcs can be combined into one calc, but for the sake of demonstrating this step-by-step I kept the calcs separate.  In fact, I usually keep things like this separate in my own workbooks so that the next person who has to make modifications can figure out more easily what is going on.

• 4. Re: Counting the number of scores above and below selected score

Thank you for the response.  I do not see the attached example.  Am I missing something?  This is only my second time posting a question so please forgive me for not being familiar with the site.  Your help is greatly appreciated.

• 5. Re: Counting the number of scores above and below selected score

So sorry.  I was able to see the attachment when opening your response in my email.  I will give the calc a try.  Thank you again.