4 Replies Latest reply on Feb 4, 2019 7:15 AM by Virginie Behr

# Graph showing average of ratio

Hi experts,

I'm really new at Tableau, so sorry if my questions are really really basic...

I get an excel with store in rows (each related to a district) and months in columns

I need to produce a graph showing for each district (selectable by a user in a drop down list) the stores and the average of the district (the measure being a ratio it should be then a calc and not an average of the stores %)

- should I pivot my months in rows to use my data in Tableau? I guess yes, by selecting every column with month and 'pivot'? Then is there a quick way to re-arrange the months not in alphabetical order but Jan, Feb, etc...?

- how do I display on the same graph every store (most granular level) and the district average (considering that my dataset only contains information at store level and a column with district information)?

• ###### 1. Re: Graph showing average of ratio

Virginie,

Welcome to the Forums.

You are correct, Pivoting will help.

Then you can use the newly created [Pivot Field Name] to make a calculated

field that converts the Month names into numbers:

CASE [Pivot Field Names]

WHEN "Apr" THEN 4

WHEN "Aug" THEN 8

WHEN "Dec" THEN 12

WHEN "Feb" THEN 2

WHEN "Jan" THEN 1

WHEN "Jul" THEN 7

WHEN "Jun" THEN 6

WHEN "Mei" THEN 5

WHEN "Mrt" THEN 3

WHEN "Nov" THEN 11

WHEN "Okt" THEN 10

WHEN "Sep" THEN 9

END

Then you can create a true date using

MAKEDATE(2018,[ConvertMonths],1)

To plot the District average as well,

you can use a dual axis:

https://kb.tableau.com/articles/howto/dual-axis-bar-chart-multiple-measures

The second axis will be a Level of Detail calculation (Overview: Level of Detail Expressions - Tableau):

{ FIXED [District],MONTH([PlotDate]):AVG([Pivot Field Values])}

Graph showing average of ratio

• ###### 2. Re: Graph showing average of ratio

Thanks a lot Swaroop, I already learnt a lot, and very useful to get your workbook!

Any idea if/how I can only show one time the district average in the legend?

I guess we need to use a trick as here it displays for every store and I don't see any simple way to change that...

• ###### 3. Re: Graph showing average of ratio

Virginie,

My apologies, I wasn't paying attention to the colors.

I removed [Measure Names] from the color shelf

of the store axis, but left it on the average axis.

However, It didn't merge the two legends into one:

• ###### 4. Re: Graph showing average of ratio

Thanks again Swaroop, that is exactly what I wanted to achieve!