Hi guys - this may be simple to you, but hopefully its a quick fix.
I have an attached Pivottable and I'm trying to do the following:
1) I would like the items to show as they are instead of "1"
2) If I am Filtering as Day Type, can't I get rid of the arrow for "Date" or Date and arrow completely?
3) How can I delete the words "Count of Item" in the table itself?
Thank you for your time as always.
Hi
Question 1:
a pivot table summarize values by different type of aggregation (sum, count, average,...) . You can e.g. put the Item in the column and the quantity in the values and the date into rows, this is more usefull i guess
Q2: Sorry, dont understand the question, which arrow?
Q3: just go into the Cell A4 and rename it or go too the value section of the field list, dropdown and select value field settings, then change the custom name to whatever you want (except already used items in the pivot table)
Hey there,
I kind of thought that would be the case. I played around with the table before I submitted here. Unfortunately, I need to have the data shown as described. I was avoiding not having to depend on the table by simply copying the data into a new sheet for whatever needs to be analyzed. With this pivot, its easier on the eyes and faster to naviagate.
With the other questions, I wanted to get rid of the text in the A4 without changing the text color and the sorting arrow in B4.
For now, I will rearrange the table as you mentioned. Thank you for your input.
Last edited by Dexter2; 01-06-2021 at 11:11 AM.
Hello, I tried to convert the text to numbers and changing to Custom format. But, I have too much data to manipulate one by one.
I believe that the attached file does what you want.
Note that the instructions for producing the ListOfItems field and adding text to the values area are listed in the linked tutorial below.
Let us know if you have any questions.
https://www.mrexcel.com/excel-tips/p...20the%20result.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
This has worked greatly! I am still working on some data and testing it, that's why I haven't marked this thread as SOLVED yet.
Without redoing this as a PivotTable, is there a way to calculate average for the data using Data Model? I only see "New Sets".
My understanding is that a Data Model's calculations are displayed using a pivot table. So I feel that you while you can calculate an average, i.e. Average:=AVERAGE(query[Quantity]), you will need a pivot table to display the result.
We may be in a better position to help if we could get an understanding of the intended goal.
Oh okay understood. The data I have when created the Data Model Pivot Table to add the measure, I was looking to calculate the Average. But, it appears because I'm using the Data Model you can't do a Calculated Field, Being that I have to use the Data Model table to show the data, that you helped me with already. I wouldn't be able to calculate the average in a regular pivot.
Is there a way to calculate the MODE or most of these text strings?
I am going to revisit post #'s 7 and 9 and see if the measure Average:=AVERAGE(query[Quantity]) modeled in the attached file, may do what you want.
Let us know if you have any questions.
Hi there, not quite. I rearranged some items. What I would like to display is the most items. So, in the current Pivottable after manually looking at all the dates you will see the most product out of California is Earpods.
If I were exhibiting the most often listed item for a region:
1. Produce a pivot table with Region and Item in the Rows area
2. Place item again in the Values area (be sure that the field is summarized by Count)
3. Use the Tabular Report Layout
4. Do not show Subtotals
5. Turn off Grandtotals
6. Value Filter the Item column to display the Top 1 Items by Count of Item
Let us know if you have any questions.
Hi there, yes thank you for that. The data I have though can only be seen as an Added Measure, which is why I am unable to use a regular Pivot.
I may be misunderstanding however on Sheet 3 is a pivot table produced from the data model.
On Sheet 2 is a pivot table produced directly from the table on the Data sheet.
Let us know if you have any questions.
Yes that's what I am trying to do with my workbook, however I just need the item (Most/Mode) not the actual count. When I move the Item into Rows and then filter by top10, a dialogue appears saying you can't move the field into that location of the report. If I leave it in Values, I don't see an option to Filter or "Top 10" it. Also, when I add the original column to Rows (which is the same data) THAT works but then I'm not seeing a way to get the Most or Top 10,
Last edited by Dexter2; 01-27-2021 at 05:27 PM.
Okay I got it mostly displayed correctly I figured it out. Thank you both of you. After doing the top 10, it still shows all the Items in each region and their count amount instead of just the Most. I'm thinking instead of Top 10, you decrease it to 1?
Dexter2: "I'm thinking instead of Top 10, you decrease it to 1?"
Correct. You could then hide the column containing the count.
My other thought is to use formulas as modeled on Sheet 3
For Region: =IFERROR(INDEX(query[Region],MATCH(0,INDEX(COUNTIF(G$3:G3,query[Region]),,),)),"")
For Item:Formula:
Please Login or Register to view this content.
Note that this proposal would require an additional column (Count) on the Data sheet populated using: =COUNTIFS([Region],[@Region],[Item],[@Item])
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks