Combining Singular Counts into a Group for a Pie Chart

building reports
1

Good morning all, hope everyone is keeping safe.

I have a requirement to show the number of times an invoice is invoiced in a specific country as a pie chart e.g.

UK - 10
France - 4
USA - 3
Germany - 1
Greece - 1
Italy - 1
Spain - 1

The only real meaningful data in this is where the countries that appear more than once or in above example 3 and above. I can filter the graph to only show countries where the count is 3 or above however is there a way to group any country that has one instance into a category of ‘Other’ ?

Example…

UK - 10
France - 4
USA - 3
Other - 4

I’m aware that i could write a variable to return only the UK, France and the USA however given the volume is dynamic then i really need it working out what 3 and above is and treating everything else as other

TIA

2

Hi Nathan,

Create the below variables
Country Count =[Count] In ([Country])
Country Grouping =If([Country Count]=1) Then “Other” Else [Country]

If you want something to happen where Country Count = 2, please add and Elseif statment.
Please let us know if this works or you have any questions.

Thanks,
Mohammed

3

Thanks for your input Mohammed

I have created the variables as you suggest and its still not working.

[Count] is derived from a Unique Invoice Reference i.e. =COUNT([Unique Invoice Reference])

Whether i use [Country Group] or [Country] in a table i still get exactly the same list of countries.

Is it something to do with the fact that the Count is always going to be an aggregated figure?

4

For Count, please use the formula exactly as I suggested, if you want to group including Others bucket due to Count by Country.

Thanks,
Mohammed