4 Replies Latest reply on Aug 23, 2016 10:17 AM by Andrew Watson

# How to work with non aggregatable data (e.g percentages) to show different data at different levels of a hierachy

I have some data on local authorities, authorities are grouped by geographic region, and all regions together are the nation (England). The data I have cannot be aggregated, it is a median figure for each authority, or a ratio. I do not have the original numerators and denominator data.

The way I want it to be set up is a barchart that can be filtered to show and individual authorities data, but when unfiltered show the national data. As the national data is not the sum of the local authorities I cannot use the standard chart filtering method. I have the data at the national level. Is there anyway of making it so that when the chart is filtered for an individual authority it shows that individual authorities data, but when an individual is not selected to instead show the national level data (which is not the sum of authority data?). Ideally I would like to be able to have the regional level in there as well.

I think I could come up with a workaround involving some dummy numerators and denominators but was wondering if there was a proper way of doing this.

Many thanks

• ###### 1. Re: How to work with non aggregatable data (e.g percentages) to show different data at different levels of a hierachy

It's worth exploring the SIZE() function. When 'all' LAs are selected it should have a different SIZE() than when 1 is selected.

I suggest you set up a simple calculation just containing SIZE() and see how the result differs based on what you have selected in the filter. Then you can base your calculated field showing the bar chart based on the result of SIZE(). For example:

IF SIZE() = 1 THEN [LA Result] ELSE [National Result] END

Obviously check the correct value to put in the size and also your correct field names.

Note you could also do a COUNTD on the local authorities selected, that might also work, i.e.:

IF COUNTD([LA Name]) = 1 THEN [LA Result] ELSE [National Result] END

• ###### 2. Re: How to work with non aggregatable data (e.g percentages) to show different data at different levels of a hierachy

Thanks, I will try this out

• ###### 3. Re: How to work with non aggregatable data (e.g percentages) to show different data at different levels of a hierachy

Thanks this seems to work:

I have an additional column of data for each authority that shows the national level data. I have one chart that is an action filter to select the authority, and I have another chart which is just a bar chart showing this calculation:

if countd([LA])>1 then MEDIAN([National value]) else sum([LA Value]) end

When I select an individual authority I get its result, when all authorities are selected I get the median of all the repeated national level data which is just the actual national level value.

thanks again

-Robindra

• ###### 4. Re: How to work with non aggregatable data (e.g percentages) to show different data at different levels of a hierachy

Great - please mark it as correct to close out this thread.