3 Replies Latest reply on Dec 3, 2012 10:12 AM by Joe Mako

ordinal ranking of weighted average calculation

I'm working on report that shows the ordinal ranking of metrics, applying a weighting to the ranking to created a weighted average. Then I want to take that weighted average calculated and rank that. I have not been successful in the last step of ranking the weighted averages. Attached is a sample workbook using Superstore Sales as an example. Can someone help?

Also I would like to be able to filter the states and keep the original rankings. Is there a way to do this?

• 1. Re: ordinal ranking of weighted average calculation

Hi,

You can filter the states and keep the original rankings using a filter based on a table calc, the easiest is to create a calculated field with LOOKUP(ATTR([State]),0) and put that on the Filters Shelf.

As for ranking the weighted averages, that's tricky because you need to sort State on the results of a set of table calcs (the Weighted Average) to calculated the ordinal rank. Tableau will let us visually do that by putting the Weighted Average as a discrete (blue) pill to the left of the State, but I haven't yet figured out how to do this inside of table calcs. The last time I did something like this, I ended up pre-computing the initial set of ranks in the data source and that way the final ranking could happen in Tableau. I've learned more since then, I'll have to think about how this might be done now.

One question, how big is your data set? I'm asking because an all-table-calc solution will likely start having performance issues when there are 10s to hundreds of thousands of cells in the view, in which case doing the initial ranking in the data source would make more sense.

Jonathan

• 2. Re: ordinal ranking of weighted average calculation

Thanks, I'll try that calculated field on the filters shelft.

my data isn't very large. I have three data tabs in Excel 23 columns and 23 rows each.

I also created another version doing all the ranking and weighing within Excel and that ones working well. Was just hoping to do more in Tableau than Excel.

Thanks!!

• 3. Re: ordinal ranking of weighted average calculation

A Jonathan pointed out, this question goes to one of the limitations of Tableau, you cannot sort the addressing of a table calc by the results of another table calc.

The best I can reasonably get without moving the ranking to before Tableau is the attached, a visual sort on the weighted value, and rounded to an integer to look more like a rank. This is not ideal, but is the closest reasonable route I currently know of within the current limitations of the software.

If you are interested in what I would consider an unreasonable route to accomplish this in Tableau without modification prior to Tableau, then a formula like this could work:

1+IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+1),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+2),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+3),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+4),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+5),1,0,0)+

IIF([Weighted Average of Metrics]>LOOKUP([Weighted Average of Metrics],FIRST()+6),1,0,0)

The logic in the formula will cause the result to have the same logic as the rank formula used in the other fields (if a matching value, same rank as previous with normal index otherwise).

I consider this an ugly route because you need a line for each potential mark in the partitions. It may be possible to use this route if you have a known quantity of marks, or do not mind adding if your mark count increases. Also I do not know the limitations of this route, you will want to test before production.