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.
Why you are using Average in LOD..What is the reason?
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.
munid category households
0100 police 1822
0100 recreation 1822
0100 water 1822
1 of 1 people found this helpful
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
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.
Thanks Jim! Yes, this works with the nested LOD and that gives what I needed - the identical household count for each category element.
Glad to help out -
It is pretty common to think of LOD's as aggregated values but actually they act like disaggregated values only at a different level - Nesting lets you get to additional levels of aggregation
Have a good weekend