Can anyone please have a look ?
So grand totals in Tableau are actually calculated at the 'Grain' (or Level of Detail) one above the Viz..and as such you do get these situations when using Table Calcs (as the Table Calc for the Grand Total is being run at a different level of Detail from the main values)...Can I ask why you are using Lookup? (is it purely to populate the blanks with zeros?
If I remove the LOOKUP part...I get the following
Once I've switched the Calculate Totals to Average
One other reason you may have been using LOOKUP is that you want the NULLs to be zeros in the Average calculation? As you can see here the NULL values aren't included as zeros in the Average Calc (so for 25th Jan Incoming is 1.5 ((1+2)/2) and not 0.375 is we have evaluated the NULLs are zero.
Hope that helps.
Thanks for a quick reply.
Yes, lookup is used only to populate blank values with 0 . (which was mandatory as per requirement). also , grand total has to be on row level. is it possible to find avg on row level and then populate the blanks with 0 ?
Yes i'm pretty sure we can do that...Just so I'm clear, would you want the average for the example I gave above to be 1.5 or 0.375? Depending how you want it calculated will change the solution.
I am attaching a screen shot which will make it clear . so i have picked start date as 1st dec, 2015 and end date is 6th dec, 2015. the data output (column pill DAY(day) shows all days except 5th dec , as there is no data on that day). lets now look at time of day (row pill) and lets focus on 10:01-11:00 . it has been divided into incoming volume and outgoing volume. so average of incoming volume will be 2+0+0+0+0 (row level ) divided by no. of days. similarly outgoing volume = 3+0+0+0 (row level) . similarly average for this will be calculated. and these averages should appear at row level for 10:01-11:00.
Hope this makes it clear.
Capture.PNG 164.1 KB
1 of 1 people found this helpful
Yes that makes sense...I thought it was the Column Grand Totals were we after!!
So the reason LOOKUP populated the NULLs (blanks) with Zeros is that it is a table calculation, and when we have 2 opposing blue pills and a table calc, Tableau envokes 'Densification'...This is a very complicated and not well documented feature (I'm not even sure it's deliberate!!). If you are interested Joe Mako has done some research into when/why it occurs and done this excellent video Tableau Request Live - Data Densification on Vimeo
As such I can keep the measures as Non-LookUps, and use an index() calculated field (also a Table Calc) to trigger the densification.
As you can see from the below I have it working
However this is in Tableau 9.0, and when I try and replicate this in 8.2 (which is the file you sent through) it doesn't do the same thing (as I said I think densification is 'side-affect' and so as they change Tableau from version to version it looks like this has been affected). I can post the 9.0 workbook for you if that'll help?...but I'll have a bit more of a play with the 8.2 version and see if I can get it doing the same thing!
Nice work, appreciate it Simon.
Can you send me the 9.0 report and let me have a look at it.
If you could figure out a way in 8.2, it would be beautiful.
...welcome to the murky world of 'Data Densification'!!! - a very useful and frustrating feature, in equal measure!!
attached is the 9.0 version...I'll have a bit more of a play with the 8.2 version and let you know if/when I get to a solution!