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!

[mattphillip]

It didn't work.

I put the inline DIM table in and then added as below.

Dimension

=Pick(Dim,if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)),'Top 10 Total','Abertay Total')

Expression

=Pick(Dim,

$(vFTEorHC)([FTE]),

$(vFTEorHC)({if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)) = {"=Rank($(vFTEorHC)([FTE]))<13"}>}[FTE]),

$(vFTEorHC)([FTE]))

Sorting Expression

=$(vFTEorHC)([FTE])-(Dim*100000)

The vFTEorHC variable changes on a button click with two possible values of 'sum' or 'count'. The FTE field replaces Quantity.

Any ideas would be much appreciated. It just comes up with no data to display .

I'll keep checking back tonight but I'm off home now as its almost 7pm here.

Thanks Sunny!

[mattphillip]

Hi Gysbert,

Thanks for the suggestion. I tried it but no luck. I've included my version of the code below. 'Fruit' has been replaced with a calculated dimension which allows the user to change the field displayed in the chart. The vFTEorHC variable changes on a button click with two possible values of 'sum' or 'count'. The FTE field replaces Quantity. I tried the below in a text box and got an error message.

='Total top 10: '& num(sum({<if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2))={"=rank($(VFTEorHC)(FTE)<=10"}>}FTE),'#,##0')

Any ideas where I'm going wrong?

Thanks in advance!

[sunny_talwar]

I think the issue is in the dimension, can you try this out:

=Pick(Dim,

$(vFTEorHC)([FTE]),

$(vFTEorHC)({<$(=if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2))) = {"=Rank($(vFTEorHC)([FTE]))<13"}>}[FTE]),

$(vFTEorHC)([FTE]))

Expression editor might continue to show error in expression, but try clicking ok to see if you get any result or not

[mattphillip]

Right I've tried that and now the chart simply comes up with 'Allocated memory exceeded' which is odd as my machine is spec'd higher than most to handle larger data and the data we're using on the current dashboard isn't huge.

Don't suppose you have any other ideas?

Appreciate your efforts

[sunny_talwar]

Unfortunately I do not. Until I can see the issue, it is very difficult to find the issue