Top N % to total

[Anonymous]

I'm looking to display the top 10 location's $ percent to total.  I have a chart to display the top 10 using a dimension expression of

=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location)

Suppressing When Value is Null

and an expression of Sum(Dollar_Demand_Net)

However, I need to see that total value's percent to the total of ALL locations.  I realize I can't get this in the same chart but hope I can get it in a text object.

The total of all locations is easy, but how do I calculate the top 10 locations in a text, object single's expression? 

p.s.

This didn't work as an expression

Sum({<Location={$(=if(aggr(Rank(Sum(Dollar_Demand_Net)),Location)<=10,Location))}>} Dollar_Demand_Net)

Thoughts...

[Jason_Michaelides]

Brilliant 🙂 Must be right!

[Anonymous]

Yup, Brilliant! Thank you swuehl!  I'm still surprised you don't need to aggr rank by Location but you don't.  Your solution works great.

[swuehl]

No, you don't need to explicitely aggregate over the field on which you want to perform an advanced search to filter sets.

Please compare with the example from the Help file:

sum( {$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales )

Same here, Sales is aggregated per Customer, hence the field name of the element_set is defining the scope of the advanced search aggregation functions.

Regards,

Stefan

[Jagveer]

HI,

I have been searching for something similar but the expression that i have is aggregated based on year so when i use your solution it says nested aggregation not allowed. Could you please help.

This is my top 10 data based on YoY change

The YoY change is calculated column

sum({<Year={2020}>} [Revenue CAD_QTD])- sum({<Year={2019}>} [Revenue CAD_QTD])

Account	2019	2020	YoY Change	% Change
	$1,898.78	$2,550.76	$651.98	34.34%
C1	$710.07	$874.23	$164.15	23.12%
C5	$70.20	$200.74	$130.53	185.93%
C4	$261.18	$380.68	$119.50	45.75%
C3	$306.82	$410.19	$103.36	33.69%
C2	$488.39	$556.05	$67.66	13.85%
C9	$25.73	$68.06	$42.33	164.49%
C23	$5.05	$15.00	$9.94	196.86%
C25	$4.15	$10.25	$6.10	147.06%
C22	$11.05	$15.40	$4.35	39.39%
C19	$16.12	$20.17	$4.05	25.12%