7 Replies Latest reply on Apr 20, 2018 12:18 PM by Jim Dehner

    Need to calculate a grand total that does not depend on dimension in the view

    John Burton



      I am doing an expense per household analysis for municipalities.  Expenses are broken out into category and sub-category dimensions for each municipality (with typical municipal expenses like recreation and protection services).  Households is its own measure field (and so it is repeated multiple times for each municipality, i.e. for each category and sub-category of expense).  I need to calculate a grand total number of households for the province that does not depend on the category dimension.  I'm using a LOD calculation to do this but annoyingly the households number is not consistent when I bring the category dimension into the view.  I assume this is because of null values (not every municipality reporting an expense for every category).  You can see what I mean in the two years highlighted in the table below (the other years give a consistent household number regardless of whether the category dimension is in the view or not).


      When I try to color an expenses per household bar chart by category it changes the height of the bars for 2014 and 2015 because of the number discrepancy.



      I've attached the workbook so you can see the fixed LOD calculation I'm using and hopefully let me know where I'm going wrong