cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Show Top # total in straight table.

Hi, I have sample data shown below. I'm looking to build a table that shows the sum of price by item, but also the total price of top 3 items and all other items. I don't think it's possible doing it directly, but wondering if there's any workaround or alternatives?

Output table needed:

 Item Sum (Order Price) Watermelon 11 Banana 9 Pear 6.6 Top 3 Total 26.6 All Other 7.7 Grand Total 34.3

Sample Data:

 Item Order Price Apple \$          1.00 Apple \$          1.00 Apple \$          1.00 Apple \$          1.30 Apple \$          1.00 Orange \$          0.80 Orange \$          0.80 Orange \$          0.80 Pear \$          2.00 Pear \$          2.30 Pear \$          2.30 Watermelon \$          3.00 Watermelon \$          2.00 Watermelon \$          5.00 Watermelon \$          1.00 Banana \$          1.00 Banana \$          2.00 Banana \$          3.00 Banana \$          1.00 Banana \$          2.00
1 Solution

Accepted Solutions
MVP

Dim:

Dim, SortValue

1, 40000

2, 30000

3, 20000

4, 10000

];

and then sort descending by the following expression:

=RangeSum(SortValue + Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]))

3 Replies
MVP

May be like this:

Script

Table:

Item,

[Order Price]

FROM

(html, codepage is 1252, embedded labels, table is @2);

Dim:

Dim

1

2

3

4

];

Chart

Dimension

=Pick(Dim, Item, 'Top 3 Total', 'All Other', 'Grand Total')

Expression

=Pick(Dim,

Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]),

Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]),

Sum({<Item = {"=Rank(Sum([Order Price])) >= 4"}>} [Order Price]),

Sum([Order Price]))

Anonymous
Not applicable
Author

Awesome! Seems to work pretty good. What would be the expression for sort order so it's sorted by total price in descending order for top 3 items, then 'Top 3 Total', 'All Other', 'Grand Total'?

MVP

Dim:

Dim, SortValue

1, 40000

2, 30000

3, 20000

4, 10000

];

and then sort descending by the following expression:

=RangeSum(SortValue + Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]))

Community Browser