Display Top N%

[balanandam]

Hi all,

I'm presenting sample data here and I need to display top 5 %'s

Sample Data

Load * Inline

[
Customer, Sales
A, 100
B, 120
C, 300
D, 400
E, 75
F, 50
G, 125
H, 2000
I, 250
];

Added calculated dimension as

=IF(Aggr(Rank(SUM(Sales),4),Customer)<=5,Customer)

expression as

SUM({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

here I need to avoid highlighted row. Adding sample app here. Pls. advise.

Br, Balanandam

[sunny_talwar]

Check 'Suppress When Value Is Null' on the dimension tab of chart properties

[Anil_Babu_Samineni]

Try to disable enable the Suppress Null value in dimension level

[sunny_talwar]

Do you mean enable?

[balanandam]

Anil & Sunny,

I tried both disable and enable. But if you see sales% column, H sales is 2000 and the actual share 58.48% (if not suppressed NULL on Dimension) and share will be 65.04% for the same when suppressed NULL on Dimension. This is the problem exactly.

Br, Balanandam

[sunny_talwar]

Try this

Dimension

Customer

Expressions

Sum({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

Sum({<Customer = {"=Rank(SUM(Sales),4) <= 5"}>}Sales)/vTotalSales

[paulferguson80]

Hi,

I may be over simplifying the question but the null is caused by the calculation.

Would it not work the way you want it to with:

Dimension - Customer

Sales#  - Sum(Sales)

Sales% - Sum(Sales) / Sum(TOTAL Sales)

Then limiting the dimension using "Show values that are" - Greater Than 5% relative to the total

and then un tick "Show Others"

I think this would give you what you want to display?

Thanks Paul

[balanandam]

Thanks Sunny, this is  so helpful. And, one more thing here is

when I click on Dimension sales% getting changed i.e 100.00%. Can we make constant i.e 8.77% ?

Sorry If my ask is not reasonable or funny ?

Br, Balanandam

[sunny_talwar · Accepted solution]

Update your variable to this

=Sum({<Customer>}Sales)

[balanandam]

Thank you so much!