You could use an index calculation to rank the Products under the specific customer. Place the index calculated field between Customer and Product and use both Customer and Product in the Addressing fields in the table calculation. Sort the index field based on your sort field. Then filter the index to show the top N values
I have attached a sample workbook wherein I am displaying the Top N products by sales for individual customers.
I believe this is what you are looking for.
The attached workbook is in version 9.3.
Step 1: Create a top N customers view
- Open a new workbook and connect to the Sample-Superstore data source.
- In the Data pane, right-click Customer Name, and select Create > Set.
- In the Create Set dialog box that opens, do the following:
- In the Name text box, type Top N Customers by Items or product name.
- Click the Top tab.
- Select By Field.
- From the field drop-down list, select product name or item name
- From the aggregation drop-down list, select count.
- When finished, click OK.
- From Sets, drag Top N Customers by Items to the Rows shelf.
- From Dimensions, drag Customer Name to the Rows shelf, positioning it to the right of the set.
- From Measures, drag Sales to the Columns shelf.
- On the toolbar, click the Descending Sort button to make sure that the set is working.
- In the Calculated Field dialog box that opens, complete the following steps:
- In the Name text box, type Subset Labels.
- In the Formula text box, type the following formula to create dynamic labels for the customers in the set:
- From Dimensions, drag Subset Labels to the Rows shelf, placing it between the Top N set and the Customer Name dimension.
- On the Rows shelf, right-click the Top N Customers by Sales set, and then select Show Header.
Step 2: Combine the Top N set with a dynamic parameter
- In the Data pane, right-click Top N Customers by Items, and then select Edit Set.
- In the Edit Set dialog box, select the Top tab, click the value drop-down menu, select the Top Customers parameter, and then click OK.
Additional tips for improving the view's functionality
Here are some additional steps you can take to give your viewers more flexibility in displaying the customer subsets.
- From the Data pane drop-down menu, select Create Parameter.
- In the Create Parameter dialog box that opens, do the following:
- For Name, type Expand or Collapse.
- For Data type, select String.
- For Allowable values, select List.
- In the List of values, type the values Expand and Collapse.
- Select Analysis > Create Calculated Field.
- In the Create Calculated Field dialog box that opens, do the following to create a calculation that uses the parameter you just created. This calculation enables viewers to specify how to view customers in the Others subset:
- For Name, enter Customer Names.
- In the formula box, type the following formula, and then click OK:
- (optional) From the Columns shelf, drag the count([Product name or item name]) measure to Label on the Marks card.
- From the Dimensions pane, drag Customer Names directly on top of Customer Nameon the Rows shelf, so that it replaces it.
Now you can use the Expand or Collapse parameter control to see the list of names in the top N customers and the remaining customers rolled up into a single Others entry.
Mat.twbx 1.2 MB
Hey Mat, there are likely several ways to accomplish this but based on the information given but I went this direction:
- Create a table calculation field using the INDEX function to control the row sort.
- Create a parameter to control your Top N selection, I called mine 'Top N'.
- Create a table calculation field to filter to the Top N, I called my 'Is Top N'.
- Use the Is Top N table calculation as a filter and set it to true. Right click on this filter and Edit Table Calculation then set it up like this:
- Show the Top N parameter control (right click on it in the parameter window and Show Parameter Control.
- Drop the INDEX table calculation on the rows shelf, set it to discrete, move it in between Customer Name and Product Name, set it up the same way you did the Is Top N table calculation filter (this is to sort your products descending by Sales).
Workbook attached. Let me know if that works for you. I also left the INDEX field header showing on the rows shelf to validate the results but you can hide that header if you want.
Top N.twbx 1.2 MB
In the future if you're going to copy/paste the contents of a kb article can you instead simply paste the URL and leave out the contents. When you copy/paste it leaves out screenshots and other important information.
Hi kenneth ,
Thank you . but i have edited some of the steps in the content so i did not post the link directly. I changed some of the calculated fields name and which need to be place in column shelf and row shelf for the understanding of his problem.
Hey Rishabh, welcome to the forums! As a tip, please read the other replies to a question before posting so as to avoid providing the same answer as others. This way we might end up with several possible solutions to a problem instead of the same solution posted by several individuals.
I had responded to this question just after a minute of it being posted and there was no other replies by then. However, I think my response got approved by the moderators just now so you might be seeing it being posted now.
Though I understand your point
My apologies Rishabh, I forgot that they still require moderation with new users.