What are the Highest, Lowest and Median Products?

What if you want to know what the highest priced product is as well as the lowest? You may not be aware but you can use the FIRST and LAST functions in Tableau. But how do you pull the product closest to the median value? In the blog below I show you a solution with pulling windows calculations and table calculations for this solution. They are faster than LOD's in some cases.

How to Pull the Highest Product

First let us figure out the most expensive piece of furniture in the Sample Superstore data set.

On a blank worksheet, put Category, and Product Name on the Rows shelf. Place Sales on the Text marks card.

You will need to sort the values by Sales Highest to Lowest on Product Name.

Additionally filter the sheet by Category = Furniture.

Create the following calculated field:

Highest

FIRST()=0

Place this on the Filters shelf and check the box for True.

It looks like the most expensive piece of furniture is the HON 5400 Series Task Chairs for Big and Tall.

How to Pull the Lowest Product

Now let's figure out the cheapest piece of furniture that is sold.

Duplicate this sheet and create the following calculation:

Lowest

LAST()=0

Replace the Highest Filter with this Lowest calculation.

Select the box for True.

It looks like the cheapest product we have is Nu-Dell EZ-Mount Plastic Wall Frames.

How to Pull the Median Product

This part gets a little complicated.

Create the following Calculated fields:

Median Sales

WINDOW_MEDIAN(SUM([Sales]))

This calculation is scanning the current sheet and pulling the median value.

Absolute Difference from Median

ABS(SUM([Sales])-[Median Sales])

The ABS function returns the absolute value of the given number. It removes the negative value and returns a positive one.

Min Difference Sales

WINDOW_MIN([Absolute Difference from Median])

This formula is calculating any differences from that median value and the Sales value.

Closest Row

[Absolute Difference from Median] = [Min Difference Sales]

Place the Closest Row calculated field on the Filter Marks card and select True.

It looks like the Novimex High-Tech Fabric Mesh Task Chair is the median product for the Furniture category.

What does all of this mean?

We are asking tableau to find the row that is the closest amount to that median value. As you see below this is a whole number and it found just one row with that amount.

This may get more complicated if you have two values with decimals that are close to that median. Tableau will display both numbers if it does not find the exact number.

Let's look at the Office Supplies Category and see if the median product changes by selecting Office Supplies on our Category Filter.

Here we can see that it returned two products instead of one.

Both products are .36 away from the median.

This is where the RANK_UNIQUE formula needs to be used.

Create the following calculated field:

RANK_UNIQUE

RANK_UNIQUE([Absolute Difference from Median],'asc')

I placed this in the view below and had to edit the Table calculation by Specific Dimensions, and checked the boxes for Category and Product Name.

Table Calculation

In this case I am selecting the higher priced product of the two.

Place this calculated field on the Filters Marks card and select 1 for the slider.

My median product is Holmes Replacement Filter for Hepa Air Cleaner.

*I also used Google AI to help me trouble-shoot this issue! I just put it in a scenario that makes sense for business users.