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
Thanks, I will try this out
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.
Great - please mark it as correct to close out this thread.