Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
http://www.contextures.com/xlPivot05.html#TopItems
I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
Epinn
In order for anything to be ranked, it must have a criterion - Number, Value, Percentage or whatever and those fields recording that information have to be in the data area if they are to be used for the purpose of ranking. Any Field in the row area can be ranked according to the criterion. Sales Person by value of sales, Category by value of sales, Region by value of sales etc. One would not normally drag Sales to the row area of a report, but you can if you want to. It could be sorted, which would have the same effect as ranking, and you could hide all but the first 10 items in the list. Alternatively, you could also repeat Sales as a data item and choose Top 10 -- Regards Roger Govier "Epinn" wrote in message ... http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
Roger,
Thanks for the info. One would not normally drag Sales to the row area of a report, but you can if you want to. << I will only do this in an experiment. I have two columns - Product Name and Qty. Looks fine. If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! Epinn "Roger Govier" wrote in message ... Epinn In order for anything to be ranked, it must have a criterion - Number, Value, Percentage or whatever and those fields recording that information have to be in the data area if they are to be used for the purpose of ranking. Any Field in the row area can be ranked according to the criterion. Sales Person by value of sales, Category by value of sales, Region by value of sales etc. One would not normally drag Sales to the row area of a report, but you can if you want to. It could be sorted, which would have the same effect as ranking, and you could hide all but the first 10 items in the list. Alternatively, you could also repeat Sales as a data item and choose Top 10 -- Regards Roger Govier "Epinn" wrote in message ... http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
Epinn
If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! I'm willing to bet that you don't <vbg Either there are duplicate numbers in your Qty, and dragging it to the row area will (quite correctly) show the value only once or Clicking the dropdown on Qty, Show All isn't selected -- Regards Roger Govier "Epinn" wrote in message ... Roger, Thanks for the info. One would not normally drag Sales to the row area of a report, but you can if you want to. << I will only do this in an experiment. I have two columns - Product Name and Qty. Looks fine. If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! Epinn "Roger Govier" wrote in message ... Epinn In order for anything to be ranked, it must have a criterion - Number, Value, Percentage or whatever and those fields recording that information have to be in the data area if they are to be used for the purpose of ranking. Any Field in the row area can be ranked according to the criterion. Sales Person by value of sales, Category by value of sales, Region by value of sales etc. One would not normally drag Sales to the row area of a report, but you can if you want to. It could be sorted, which would have the same effect as ranking, and you could hide all but the first 10 items in the list. Alternatively, you could also repeat Sales as a data item and choose Top 10 -- Regards Roger Govier "Epinn" wrote in message ... http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
I'm willing to bet that you don't <vbg
I was going to say how much $$$ but then I don't want to turn this forum into online gaming. ;) Either there are duplicate numbers in your Qty, and dragging it to the row area will (quite correctly) show the value only once. Yes, I have covered that already, but still ...... There has to be a reason and I haven't figured it out yet. I haven't told you that the PT was based on *two* sheets with identical column headings. I have taken two sheets into consideration as well. But that shouldn't make a difference between dropping the field into data area vs. row area. If I still haven't found the reason later today and if you want to see my PT, I can e-mail it to you and I know your e-mail address. Epinn "Roger Govier" wrote in message ... Epinn If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! I'm willing to bet that you don't <vbg Either there are duplicate numbers in your Qty, and dragging it to the row area will (quite correctly) show the value only once or Clicking the dropdown on Qty, Show All isn't selected -- Regards Roger Govier "Epinn" wrote in message ... Roger, Thanks for the info. One would not normally drag Sales to the row area of a report, but you can if you want to. << I will only do this in an experiment. I have two columns - Product Name and Qty. Looks fine. If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! Epinn "Roger Govier" wrote in message ... Epinn In order for anything to be ranked, it must have a criterion - Number, Value, Percentage or whatever and those fields recording that information have to be in the data area if they are to be used for the purpose of ranking. Any Field in the row area can be ranked according to the criterion. Sales Person by value of sales, Category by value of sales, Region by value of sales etc. One would not normally drag Sales to the row area of a report, but you can if you want to. It could be sorted, which would have the same effect as ranking, and you could hide all but the first 10 items in the list. Alternatively, you could also repeat Sales as a data item and choose Top 10 -- Regards Roger Govier "Epinn" wrote in message ... http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
PivotTable - show top items
Roger,
I have started a new thread in the Excel functions Newsgroup. Subject is PivotTable ...... Date is today. I don't think you want to bet with me. ;) Epinn "Roger Govier" wrote in message ... Epinn If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! I'm willing to bet that you don't <vbg Either there are duplicate numbers in your Qty, and dragging it to the row area will (quite correctly) show the value only once or Clicking the dropdown on Qty, Show All isn't selected -- Regards Roger Govier "Epinn" wrote in message ... Roger, Thanks for the info. One would not normally drag Sales to the row area of a report, but you can if you want to. << I will only do this in an experiment. I have two columns - Product Name and Qty. Looks fine. If I just drag qty to the row area and nothing else, I lose a couple of records. Scary! Epinn "Roger Govier" wrote in message ... Epinn In order for anything to be ranked, it must have a criterion - Number, Value, Percentage or whatever and those fields recording that information have to be in the data area if they are to be used for the purpose of ranking. Any Field in the row area can be ranked according to the criterion. Sales Person by value of sales, Category by value of sales, Region by value of sales etc. One would not normally drag Sales to the row area of a report, but you can if you want to. It could be sorted, which would have the same effect as ranking, and you could hide all but the first 10 items in the list. Alternatively, you could also repeat Sales as a data item and choose Top 10 -- Regards Roger Govier "Epinn" wrote in message ... http://www.contextures.com/xlPivot05.html#TopItems I use the data file from the above site. I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem. However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items. My interpretation is that we can only show top items on data fields and they must be in the data area. Any comments? Epinn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to show all items with an "x" next to them | Excel Worksheet Functions | |||
show items with no data option in pivot tables | Excel Discussion (Misc queries) | |||
Finding unique items in data field for pivot tables | Excel Discussion (Misc queries) | |||
PivotTable - calculated items? | Excel Discussion (Misc queries) | |||
pivottable - show all not appearing | Excel Worksheet Functions |