2 Replies Latest reply on Jul 5, 2016 3:12 AM by lalitha p

# Display Top 20, Bottom 20, 20-40, 40-60 based on another sheet

Hi Everyone,

I have a dashboard as attached. I have 2 sheets to develop here.

My Sheet1(# Customers with Total Sales ) is showing count of all customers

Logic :

calculation1:

If sales > 100000 and sales < 500000 then 0-10%

elseif sales >=500000 and sales < 1000000 then 20-30%

elseif  sales >=1000000 and sales < 2000000 then 40-60%

elseif sales >=2000000 and sales <= 3000000 then 60-80%

else 80-100% end

The above is my logic for sheet1 and confirm me this is the way i have taken is correct or not to get the capture1 report.

I have dragged calculation1 to columns and count of customers { count(customer name) } to Rows.

Sheet 2:

Sheet 2 is displaying all the customer name and sales value in the bar.

A filter should be available in the top which is having values lik this: Top 20, Bottom 20, 20-30, 30-40, 40-50 etc.,

Now i need to have an interaction to sheet2 from sheet1 like this.

case 1:

If no interaction then Sheet2 should display default top 20 values.

case 2:

If you click on any bar in sheet1, say it is having "69" under 28%-60% then in sheet 2 out of "69" top 20 should display if user change the value to Bottom 20 then bottom 20 shud display(out of "69" only)

Like this it shud go.,.

• ###### 1. Re: Display Top 20, Bottom 20, 20-40, 40-60 based on another sheet

Hi Lalitha,

can you please share the sample data or is it possible to share the workbook?? sales values can be dynamic. is there any reason you have hard coded the same in the calculated field?

-Ashish

• ###### 2. Re: Display Top 20, Bottom 20, 20-40, 40-60 based on another sheet

I Haven't hard-corded those values . I am showing here count of customers in that sales range . Though sales is dynamic instead of sum i will consider avg. ( Imagine, As of now my sales value will be in that range ). If customer1 is having sales figure 199555 then he shud be in one range and if cusotmer 2 is having sales with 398787 then he shud belongs to one range as per my calclulation.