14 Replies Latest reply on Mar 8, 2016 11:41 PM by Eshwar Prasad

# Top % Categories

I have requirement like i need to show the top categories (5%, 10%, 15 %, 25%, 50%)

Members (who will in top 5%) Sales amount % out of Total sales. LIke this i need to get for 10% etc..

Below is the Example screen shot. PFA workbook.

• ###### 1. Re: Top % Categories

No a perfect answer but something here.

Actually I have not clearly understood all the mechanism of TOP N%, but I think this works in this case,

To determine Top N%, "Customer Name" is needed in detail, which create slice of bar charts.

[Share %]

{fixed [Customer Name]:sum([Sales])}/{exclude [Customer Name]:sum([Sales])}

[Top% Filter]

RUNNING_SUM(sum([Share %]))

[Top5% sales]

(if [Top% Filter]<5/100 then running_sum(sum([Sales])) end)

Similar formula with changing "N%" part for 10%, 25%, and 50%.

Then put 4 fields into measure values.

And put "Customer Name" in detail.

Then edit Table calc on each measure value to add "Customer Name".

Put "Top 50% sales" in label which can cover 5%, 10%, 25% together.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Top % Categories

I did in the same how you did. I am getting all zeros for all % Categories.

i am missing anything here?

• ###### 3. Re: Top % Categories

Did you put "customer name" field in to detail?

Shin

1 of 1 people found this helpful
• ###### 4. Re: Top % Categories

Yes Shin.

Is there any relation to first view and second view?

• ###### 5. Re: Top % Categories

I tried in the attached book.

PFB screen shot which i got ..

can you please tel me where i am missing..@

• ###### 6. Re: Top % Categories

I'm sorry my past reply was based on some trial and errors and I was not able to explain everything.

Now I revisited and found couple of things.

2. Edit Measure's Table calculation

X 4 times for 10%, 25%, and 50%

The strange thing I found here is that after I changed calculated field selection,

I hit edit again, it shows "Top50% sales" instead of  "Top% filter".

I guess this is a kind of refresh?

Enjoy!!

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: Top % Categories

Thank you so much shin.

Is this is the only option to do this.

as it is stacked bar (Customer name) actions are not working properly.

PFA. Color cofing is not working properly. if i am clicking and going to the drill down specific religion all members are  not coming.

@

• ###### 8. Re: Top % Categories

Please mark the answer as correct . so that the for the other users it will very easy to find the solution.

Thank you,

Happy Vizzing

• ###### 9. Re: Top % Categories

HI Shin,

Actually what we got in the Top Categories are not actual result.

Below are the accurate result in 5%(\$12,258,865) and 10%(\$18,319033).

But in the POC we are getting 5%(\$1,504,930) and 10%(\$3,029,825) which is suspected.

Can you help me to get this proper result. PFA.@

• ###### 10. Re: Top % Categories

In this TOP% situation, filtering data brings different reslults.

Then my approach is keeping data set as same, but just hide.

If you need to align Y-axis, set reference line with "None" line to both 5% and 10% graph.

Thanks,

Shin

9.0 attached

• ###### 11. Re: Top % Categories

Hi shin,

My concern is TopN% is not giving acurate result.

Below are the accurate result in 5%(\$12,258,865) and 10%(\$18,319033).

But in the POC we are getting 5%(\$1,504,930) and 10%(\$3,029,825) which is suspected.

almost 1 million amount difference is coming..

• ###### 12. Re: Top % Categories

am confused.

where/how you got accurate result?

Why 5% x 2 = 25M is so much different from 10% = 18M ?

Total sales without filtering says 30,434,456   correct??

Thanks,

Shin

• ###### 13. Re: Top % Categories

Shin,

I have analysis with  me. i did the validation as below.

Total members count is 2,211.

in that 5% means 110.

what i did is i took all customers and and their sales. and then i sorter the Customer based on sales. then i filter top 110. so for this top 110(which are belongs to top 5%) Members will get (\$12,258,865)

Total sales amount is 30,434,456 but out this Top 5% members spending amount is (\$12,258,865).

i think the stacked bar is showing Highest customer sales. we should sum up all the sales amount of all stacks(customers)??

am i missing anything here??/

• ###### 14. Re: Top % Categories

PFA file for reference.