6 Replies Latest reply on Oct 15, 2018 8:36 PM by elias.daniels.0

# (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Hi all,

Can you please share some light!

You guys might help me with some knowledge.

I've attached both Excel and Tableau files.

(1) Pivoting (Grouping) Data Sources

I have two data sources that need to be summarised (to avoid duplication) in order to link them and use fields in both tables I require. (Sales and Cases).

I know that in SQL I could join both queries to obtain what I want (two Sums from different tables), I do not know how to do it in Tableau if the source is coming from Excel.

Data Source 1: Spend

Data Source 2: Cases

Both data Sources can be summarise to 1 row. For example I dont need City, Ticket Code or Source.

What I need is Country, week number, Cases and Sales.

Is there a way that in Data Sources in Tableau I can choose which fields to use, therefore summarise a data source?

This is what I'm aiming to obtain:

(2) Average for Aggregate Calculated Measure

If first option is not possible.

As you can see the Ratio Measure is an aggregation of Sales/Cases. But the Grand Total (Average) is not calculating the average of the ratios, is calculating the average of cases, average of Sales, then it divides these to obtain the average of Ratio. This is mistaken.

I want to the average of 1.35,0.42,0.75,5.22 and 0.2 = 1.59.    No 1.31.

Cheers,

Elias

• ###### 1. Re: (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Union option is also available in Tableau with excel files.

See attached file and check if this is what you needed.

• ###### 2. Re: (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Hi Ombir,

Unfortunately I cannot create a simple ratio of Sales/Cases. It just comes blank.

Secondly I cannot add Grand Totals Column.

Thirdly, can you please explain how did you create that join in Tableau, the one I was created just duplicate my data.

Elias

• ###### 4. Re: (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Hi Oussama,

Unfortunately I still no been able to estimate the ratio. I can do the grand total of each measure by itself but not the ratio.

Thanks,

Elias

• ###### 5. Re: (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Is this what you want ?

1 of 1 people found this helpful
• ###### 6. Re: (1) Pivoting (Grouping) Data Sources /  (2) Average for Aggregate Calculated Measure

Hi Ombir,

Yes this is what I want.

I was missing the LOD.

Thanks for the help.

Cheers,

Elias