-
1. Re: Incorrect totals with joined data
Simon RuncNov 23, 2018 8:59 AM (in response to Paul White)
1 of 1 people found this helpfulhi Paul,
So you can get around you duplicate row problem with an LoD, as you've identified.
If you use this expression....
[Sum of Beds - LoD]
{FIXED [Area], [Care Home]: MAX([Beds])}
you can bring this in as a sum, and so Tableau will know what to do!
You can then use SUM in your Calls per Bed, on this field like
[Calls Per Bed _SR]
sum([Calls])/sum([Sum of Beds - LoD])
and Tableau will calculate the GT fine.
The reason Tableau does this, btw, is that the Grand Totals are a separate calculation, where Tableau removed the levels (Area in your case) and recomputes it. This means the MAX([Beds]) is now the Max of the Care Homes within an Area and not the SUM of the Max.
One final thing. As FIXED LoDs are computed before any regular filters are applied, if you want to use your date (for example) as a filter you either need to add [date] to the left hand of the LoD expression, or make the filter a context filter (this bumps it up the calculation pipeline, so the filter is applied before the LoD is computed)
Hope that helps and makes sense, but let me know if not
-
2. Re: Incorrect totals with joined data
Paul White Nov 24, 2018 7:04 AM (in response to Simon Runc)Excellent Simon - that solved my problem. I knew the answer lay in a LOD !
Many thanks for your help - this will be really useful in a lot of my Tableau work.
Regards