Sub-total for Top 10 values

[mattphillip]

Hoping someone can help. I have a bar chart with the displayed values limited to the Top 10. What I'd like to calculate is the sub-total for that Top 10 (e.g. if the top values are 20, 15,10,6 then sub-total = 51). I also need to subtotal the entire data (but I think I can handle that one).

Anyone have any ideas?

Much appreciated!

[Gysbert_Wassenaar]

Where do you need to display these subtotals?

[sunny_talwar]

Do you have a sample you can share where you are trying this out?

[mattphillip]

Hi Gysbert,

To be honest I'm not too fussy. Either on the chart itself or in a textbox whichever is easier.

M

[Gysbert_Wassenaar]

The overall total can be added by enabling the Show Total option on the Dimension Limits tab. The top 10 total would be something like

='Total top 10: '& num(sum({<Fruit={"=rank(sum(Quantity))<=10"}>}Quantity),'#,##0')

[mattphillip]

Hi Sunny,

Please see attached as requested.  Sorry I had to put together a mock version of my dashboard due to data protection.

M

[sunny_talwar]

This?

Script:

LOAD Fruit,

     Quantity

FROM

[Top 10 total test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dim:

LOAD * Inline [

Dim

1

2

3

];

Dimension:

=Pick(Dim, Fruit, 'Top 10 Total', 'Overall Total')

Expression:

=Pick(Dim,

Sum(Quantity),

Sum({<Fruit = {"=Rank(Sum(Quantity)) < 11"}>}Quantity),

Sum(Quantity))

Sorting Expression: Descending

Sum(Quantity)-(Dim*100000)

[sunny_talwar]

Forgot to bump the dimension limits by 2

[mattphillip]

Thanks Sunny. Now just to make it tricky - on my actual dashboard, instead of 'Fruit', I have a calculated dimension which allows the user to change the field in the chart by a click on a button (=if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)) ). Am I able to simply replace 'Fruit' with this or does this only work with typical dimensions?

M

[sunny_talwar]

I would think it should work. Give it a try and see if that works or not