5 Replies Latest reply on Apr 21, 2017 5:54 AM by Juanita Simmons

    LOD Calc Totals Change When Dimension is on row vs column

    Juanita Simmons

      Hello Tableau forums--

       

      I don't have a workbook yet, but has anyone else experienced an LOD calculation performing differently when dimension pills are moved from the column to the row shelf? I'm trying to get an average for the company even when I'm filtered on one facility (healthcare data). I see the correct company average when both of my Fixed dimensions are on Rows, but when I move one to columns I get a totally different company average... Will post mock workbook soon.

       

      Thanks,

       

      Nita

        • 1. Re: LOD Calc Totals Change When Dimension is on row vs column
          Tom W

          It shouldn't change it, however there might be something else at play or your LOD is misconfigured. I'll take a look at the sample when you psot it.

          • 2. Re: LOD Calc Totals Change When Dimension is on row vs column
            Galen Busch

            Hi Juanita,

             

            I'm assuming you're using {INCLUDE : }

             

            Try using fixed instead.


            Include leverages the level of detail (granularity) of your view, while fixed will ignore the granularity and return the same value for the grain defined in your clauses regardless of the level of detail of your view.


            Galen

            • 3. Re: LOD Calc Totals Change When Dimension is on row vs column
              Juanita Simmons

              Thanks Galen for your suggestion! I am actually using Fixed... Let me post the calc below:

               

              (SUM({ FIXED   [Discharge Type], [Date Filter] :

              SUM({EXCLUDE  [Big Group], [Big Group subset], [Actual Facility]:([Avg Length of Stay in Hospital])})}*[Number of Hospital Cases])/ SUM([Number of Hospital Cases]))/

              (SUM({ FIXED  [Discharge Type], [Date Filter] :

              SUM({ EXCLUDE [Big Group], [Big Group subset], [Actual Facility]: ([Avg of Standard Length of Stay in Hospital])})}*[Number of Hospital Cases])/ SUM([Number of Hospital Cases]))

               

              When I have the dimensions in the Fixed portion on rows, and the dimensions in the Exclude portion on columns--that works perfectly. When I take the Discharge Type to columns (which is where I actually need it), the company ratio for each Discharge Type is perfect, but the subtotals and grand totals for the EXCLUDE dimensions are off. I want to see the company average totals like I do when Discharge Type is on rows.

               

              Hope it's not too confusing! :-) Thanks for the help!

              • 4. Re: LOD Calc Totals Change When Dimension is on row vs column
                Justin Larson

                It would be pretty difficult from this vantage point to be able to diagnose specifics without seeing a sample. That said, one thing I would suggest is that you break out each of the LOD calculation into individual measures that are named what the subcalculation represents. Then you can drop them into the view one by one to make sure the component of your larger calculation is producing results you are expecting. Then you can relate those calculations together with a larger wrapping calculation to combine them. This makes the larger context easier to read as well, because the formula turns into something more akin to [logical_thing1]/[logical_thing2] instead of [complex multilayered onion calculus] / [newton's law of^ 4(hypotenuse camel)/sigmoid curves]+2

                 

                Debugging calcs can be pretty tricky when you can't see the interim results and breaking it apart help out in that regard a lot. When I start getting models that require this kind of complexity I start using the folder functionality to organize interim calculations as well.

                • 5. Re: LOD Calc Totals Change When Dimension is on row vs column
                  Juanita Simmons

                  HILARIOUS!!!

                   

                  Thanks for your suggestion, Justin. 1. Because it made me LOL, which is always a good start to a Friday morning :-) and 2. Because I was kinda thinking I should do that too, so you helped me to confirm the path I'm about to go down. If it doesn't work, which I'm hoping won't be the case, I'll definitely get a workbook out here soon.

                   

                  Thanks again!