9 Replies Latest reply on Oct 12, 2016 7:43 AM by Mary Gatmaitan

    Fixed LOD and Dimension Filters

    Jason Casey

      Hello All,

      I have a conundrum involving the use of a FIXED LOD expression and a dimension filter.  It feels like I should know the answer to this, but at present it eludes me.

       

      Problem setup:

      In my example workbook, I have Counts of applicants by Department and *** across four Years.  In a crosstab at the LOD equivalent to my data's granuarity, I'd see 150 applicants across the four-year period, no Males for Department B in 2012 and no Females for Department C in the same year:

      I want to build a filtered table to show the proportion of Female (or Male) applicants to each Department across the time period looking like this:

       

       

      My LOD Total calculation is the expression:

      {

      FIXED

        [Department]

        ,[Year]

      :

        SUM([Count])

      }

       

      and Pct uses this total to get the percentage:

      SUM([Count]) / SUM([LOD Total])

       

      The problem is that Department C should show 35 total applicants instead of 33.  If I add Year to the viz, I can see that the LOD Total is ignored for that Department in 2012:

       

      I recognize that I could fix the first viz by removing Year from my FIXED expression, but I would lose the ability to drill down to the year level (at least without creating a second LOD Total at that level of detail).

       

      I assume that the empty cell is causing the total to be ignored for the affected row.  (i.e., There are no Female applicants for Department C for 2012, and there is no corresponding row in the data for that Department-Year-*** combination).   Is this true?  Or is something else going on?  Most importantly, is there some means for me to fix my viz to get the correct totals?

       

      Many thanks in advance for any helpful advice!