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

Hi,

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

Thanks!

• ###### 1. Re: Need to calculate a grand total that does not depend on dimension in the view

Hi John,

If I understand correctly, you want same values per year. So, You just need to keep Year  in the view for your LOD. I just did that and I get this

Thanks

Deepak

• ###### 2. Re: Need to calculate a grand total that does not depend on dimension in the view

Thanks Deepak.  I'm not sure how you got those numbers but since I need the total households for the province they should be around 7-8 million.  You're right that I need the numbers to be the same in each year, regardless of the category.

• ###### 3. Re: Need to calculate a grand total that does not depend on dimension in the view

Why you are using Average in LOD..What is the reason?

• ###### 4. Re: Need to calculate a grand total that does not depend on dimension in the view

Because the households are repeated for each municipality.  There are rows for each expense category for each municipality.  So I average across the duplicate household numbers within a municipality and then sum across all municipalities.

E.g.

munid     category     households

0100     police          1822

0100     recreation     1822

0100     water               1822

• ###### 5. Re: Need to calculate a grand total that does not depend on dimension in the view

Hi John

is this what you would expect to see in a text chart

Not totally clear on what you wanted to do with the muni - I left them in but could have just as easily taken them out

the formulas are

and the Dollars per category are

without muni the  hh formula becomes

and the dollars filtered for muni is

and the dollars /hh is

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 6. Re: Need to calculate a grand total that does not depend on dimension in the view

Thanks Jim!  Yes, this works with the nested LOD and that gives what I needed - the identical household count for each category element.