12 Replies Latest reply on Mar 15, 2018 9:19 AM by Amanda Lamb

# Median without duplicates

There might be a very simple solution to this problem, but I can't figure it out. I want to calculate a simple median, but since some rows are duplicated it isn't coming out correctly.

My dataset looks like the table below. The "Length" field is just CompleteDate-IntakeDate. I want to calculate the median based on the length field, but because there are duplicate ID nos (because of different "Types" in this faked sample), the calculation is wrong. For example, the median on the table below comes to 33.5, but if you remove the duplicate IDnos, you get 30.5. I want the 30.5 in Tableau. The purpose is to calculate the median length by intake year.

 Idno Type IntakeDate CompleteDate Length 1 C January 3, 2017 February 14, 2017 42 2 P January 3, 2017 February 7, 2017 35 3 C January 5, 2017 February 2, 2017 28 4 C January 6, 2017 February 3, 2017 28 5 P January 6, 2017 January 29, 2017 23 6 C January 9, 2017 February 14, 2017 36 7 C January 10, 2017 February 6, 2017 27 9 P January 12, 2017 February 14, 2017 33 10 P January 13, 2017 February 3, 2017 21 11 C January 13, 2017 February 16, 2017 34 12 C January 18, 2017 February 14, 2017 27 12 P January 18, 2017 February 14, 2017 27 13 C January 18, 2017 June 8, 2017 141 13 P January 18, 2017 June 8, 2017 141 16 P January 19, 2017 February 16, 2017 28 17 C January 19, 2017 May 2, 2017 103 17 C January 19, 2017 May 2, 2017 103 17 P January 19, 2017 May 2, 2017 103
• ###### 1. Re: Median without duplicates

here is the Solution;

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to close Thread

2 of 2 people found this helpful
• ###### 2. Re: Median without duplicates

The calculation worked fine and it seems to have removed the duplicates, but I think it calculated the median for everything, and I want median by intake year. It's just saying the median every year is 30. I am not very familiar with LOD calculations so I may have missed something.

• ###### 3. Re: Median without duplicates

{MEDIAN({FIXED [Idno], [Intake Year]:AVG([Length])})}

• ###### 4. Re: Median without duplicates

Same thing.

• ###### 5. Re: Median without duplicates

Can you provide the dataset new one. Just give me few lines.

• ###### 6. Re: Median without duplicates

This Should Work:

{FIXED [Intake Year]:MEDIAN({FIXED [Idno]:AVG([Length])})}

• ###### 7. Re: Median without duplicates

This is not the same as the table above. This one is a median of 34 if you remove the duplicates, and 35.5 if you keep them in.

• ###### 8. Re: Median without duplicates

Amanda,

Make a calculated field "One Value each ID" instead of SUM(Length):

IF LOOKUP(MIN({ EXCLUDE [Type]: SUM([Length])/COUNT([Type])}),1)

=(MIN({ EXCLUDE [Type]: SUM([Length])/COUNT([Type])})) THEN

MIN({ EXCLUDE [Type]: SUM([Length])/COUNT([Type])})

ELSEIF MIN({ FIXED [Idno]:COUNT([Type])})=1 THEN SUM([Length])

END

You will get the desired Median.

Hope it helps.

Michael Ye

3 of 3 people found this helpful
• ###### 9. Re: Median without duplicates

So, I am now giving you both Overall and by Intake date. Have a look

1 of 1 people found this helpful
• ###### 10. Re: Median without duplicates

I added a packaged workbook to the original post to show what I am trying to do. The data is fake, but based on the fields and variable types I am working with. The "intake length" is not based on a different end date so the numbers aren't going to match the original.

These solutions both get me to a correct calculated median, but it remains static even as I add filters. Is there a way to calculate an accurate median that is dynamic?

• ###### 11. Re: Median without duplicates

You need to add filters to Context in this case.

Thanks

Deepak

• ###### 12. Re: Median without duplicates

That works, thanks!