Percentage of top ten volumes?

[richnorris]

I have a chart showing the regions with the top ten volume, showing the total volume for each of the top ten regions, and then the % of all total volume that this constitutes. I also want a total at the bottom. The trouble I'm having is that this % showing is coming out with a total of 100%, even though the regions not shown in the chart have some volume.

The way I am achieving the top ten, is by having an expression 'Rank'

if(rank(sum([Volume])) <=10, rank(sum([Volume])))

I am sorting on this, ascending, and on the presentation tab hiding the column, and setting max number to 10.

I have a dimension of Region,

to get the volume I simply summing volume,

and to get the percentage I am doing the following: sum([$(vCurrency) Volume]) / sum({1} TOTAL [$(vCurrency) Volume])

I am then setting total mode to Sum of rows.

Does anyone know why I am getting 100% for the total, when it should be closer to 60%?

thanks

[rwunderlich · Accepted solution]

The max visible number approach won't work in this case. Instead, filter the top Region using a Calculated DImension.

=if(aggr(rank(sum([Volume])), Region) <=10, Region)

With that method, there is no need for the hidden Rank column or max visible.

-Rob

[richnorris]

Thanks Rob thats Excactly what I needed!

[richnorris]

Hmm, its not exactly right actually, because now there is a null value showing up in the list of regions, which represents all the regions outside of the top ten? Surely the whole point of setting a calculated dimension is to say, only show these things?

[richnorris]

Oh, obviously just suppress when value is null for the dimension. Silly me, sorry. Thanks again Rob!