4 Replies Latest reply on Sep 7, 2018 9:34 AM by Simon Runc

# Tableau task help ( numbers don't match)

HI Guys

I am currently working on a task where i am transferring an excel pivot on to tableau and i need to display something like the below  ( numbers should match the below)

Prod groupProd
April 2018
May 2018
BestProject5418138

The problem is I cant get the  numbers to match the above on tableau, when i include the opp id in the rows ( please see the attached)  it gets two numbers which add up to the above as soon as i remove this ( which i need to) i get completely different number.

i have attached my workbook i am working with, i would be very grateful for any help.

Thank you

• ###### 1. Re: Tableau task help ( numbers don't match)

hi Imran,

So the issue here is that your Calculation Level of Detail (requiring OppID to compute it correctly) and Display LoD is different.

We can get round this by creating the AVGs you want at the level you want with a FIXED LoD

[Sales per Month LoD]

{FIXED [Pro Group],[Prod],[Opp  ID],(DATEPART('year', [Month])*100 + DATEPART('month', [Month])): AVG([sales per month])}

btw  this part is just the MY(Month) in the filter (DATEPART('year', [Month])*100 + DATEPART('month', [Month]))

This creates the AVGs you need 54 and 18,084 for each OppID, but as an LoD we can then SUM these when we remove the OppID from the Level of Detail

Hope that helps, and makes sense.

• ###### 2. Re: Tableau task help ( numbers don't match)

Hi Imran,

I am not sure if 18138 is correct for May. If you are looking for Average then by definition avg = total sum of all the numbers / number of items in the set

Opp ID 21 -- Avg Sales for April is 54

Opp ID 63 -- Avg Sales for April is 0 or null

Final Output:

54+0 = 54  &  54/1 = 54 which shows Correct output

Opp ID 21 -- Avg Sales for May is 54

Opp ID 63 -- Avg Sales for May is 18,084

Final Output:

54+18084 = 18138  &  18138/2 =  9069 which shows Correct output

Can you check and verify the above?

• ###### 3. Re: Tableau task help ( numbers don't match)

Hi Anik ,

thank you for your help, but no this is not correct, i want to show, the final answer as 18138

can this be done?

• ###### 4. Re: Tableau task help ( numbers don't match)

hi Imran,

Have a look at the solution i provided, it gets you to the 18,138