2 Replies Latest reply on Nov 24, 2018 7:04 AM by Paul White

    Incorrect totals with joined data

    Paul White
    Paul White Nov 23, 2018 8:50 AM

      Hi all,

       

      I'm pretty new to Tableau and I've run into a problem.

       

      I'm trying to work out the number of calls per bed for a number of care homes. Each care home is assigned to an area. The calculations for the individual homes works OK, but when I try to get the number of calls per bed for an area the calculation isn't working correctly. Clearly this is due to me joining multiple calls records for each home to single records for the number of beds.

      Screenshot.jpg

      I've checked on the community and on other websites and the answer seems to lie in the use of a level of detail/fixed expression but I can't get this to work.

       

      I've also tried blending the data but couldn't get this to work either.

       

      Thanks in advance for your help.

       

      Regards

        • 1. Re: Incorrect totals with joined data
          Simon Runc
          Simon Runc Ambassador Nov 23, 2018 8:59 AM (in response to Paul White)

          hi 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

          1 of 1 people found this helpful
          • 2. Re: Incorrect totals with joined data
            Paul White
            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