2 Replies Latest reply on May 9, 2018 7:17 AM by Fabio Fantoni

# Average of Ranking based on Average

For a certain data range, for a specific dimension, I need to calculate the average value of a daily rank based on the average value.

First of all this is the starting point:

This is quite simple and for each day and category I get the AVG(value) and the Ranke based on that AVG(Value) computed using Category.

Now what I need is "just" a table with one row for each Category with the average value of that rank for the overall period.

Something like this:

I tried using the LOD but it's not possble using table calculations inside LOD so I'm wondering if I'm missing anything or if it's even possible in Tableau (but I'm pretty sure it is )

You can find the workbook with the raw data at the following URL or attached
Tableau Public

• ###### 1. Re: Average of Ranking based on Average

Fabio,

You can get those numbers using table calculations.

If you check on the sheet validateData

1- I get the  sum of the rank averages with

WINDOW_SUM( [Value (Rank)] )

where Value(Rank) is

RANK(AVG([Value]))

this is a nested calculation, so you need to set a specific computing on each

Value(Rank)  computes using  Category  and

Value (Rank winSum) computes using  Category, Day of Date  Restarting every Category

2- I get the number of Days with

WINDOW_SUM( COUNTD( DATETRUNC('day', [Date]) ) )

computing on Day of Date

3-So you get  Value (Rank Avg) with

[Value (Rank  winSum)] / [Day (Number of)]

4-And to  show a single value per Category, I build a filter  'keep single date' with

if index() = 1 then 1 else 0 end

computing on Day of Date

The finalView sheet is a duplicate of the validateData sheet  where I

1-move keep single date  to the filter shelf and keep the  1s

2-keep only the Value (Rank Avg)  measure

3-Hide the Day(Date)  Header

Michel

• ###### 2. Re: Average of Ranking based on Average

Thanks a lot Michel!

Really useful