4 Replies Latest reply on Aug 10, 2015 6:20 AM by bharathwaj vijayakumar

# Window Median for a column without adding the column to the dimension

Hello,

I have student graduation information by ID. I am doing a count of IDs by major every year to show how many students graduated in each major every year. Major is in row shelf and year is on column shelf. Now I need a median number of students graduated every year by comparing the majors, So if there are five majors in 2011 with 30, 40, 50, 60, 70 degrees conferred respectively, then the median should be 50 for 2011. I realized window median is the best way to do it but, the major has to be in the dimension in order for the window median to work. I want to take the median of degrees conferred in each year by comparing all the majors in the year and without adding major to the dimension. Only the year should be in the dimension. Is this possible? I am attaching the sheet for your reference

Vijay

• ###### 1. Re: Window Median for a column without adding the column to the dimension

Hello Bharathwaj,

Since you are on V9, you can use an LoD here to generate the same data as Window Median. Given your example workbook, I cam up with something like this

MEDIAN({ INCLUDE [BNR MajorNameCollapsed IR] : COUNT([Student ID]) })

With LoD, specifically INCLUDE, we can address a Dimension without it having to be in the Viz Level of Detail, while still taking into account the Viz Level of Detail.

In this case, since Academic Year will be in the Column Shelf, it will be considered in the Aggregation.

Therefore, we can return the Count of Student ID per major, and Find the Median of that per year.

What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran

Let me know if that makes sense, or if you need some more assistance.

Regards,

Rody

• ###### 2. Re: Window Median for a column without adding the column to the dimension

This is fantastic. I didn't know this exists. Thank you very much.

• ###### 3. Re: Window Median for a column without adding the column to the dimension

No problem, glad I could help!

LoD are really powerful, and can give us a lot deeper insight into our data.

Here is a great resource for LoD articles and How to's

lod expression | Drawing with Numbers

Regards,

Rody

• ###### 4. Re: Window Median for a column without adding the column to the dimension

Thank you. I checked and I think it will be very helpful for me.