LinkBack URL
About LinkBacks
Hi, I wanted my pivot to exclude blank/zero field. How do i do it? And also, i cant seem to be able to get the Grand Total Function working in my excel. Y? Anyone can help?
Look easy but somehow i not able to solve...
Hi, I wanted my pivot to exclude blank/zero field. How do i do it? And also, i cant seem to be able to get the Grand Total Function working in my excel. Y? Anyone can help?
Look easy but somehow i not able to solve...
Could you attach excel file with example how it should be? Make sure your desired results are shown, mock them up manually if necessary.
Here. Please see attached.
Thanks
Something like this?
Sandy can you explain how do you do it?? Fantastic jobOriginally Posted by sandy666
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
@shukla
You can do it without problem with Ex2016. Use PowerQuery
jp16 should install add-ins: PowerQuery for Ex2010 and PowerPivot for Ex2010 first then use PowerQuery, unpivot product columns, and create PivotTable from "new" table
That I know but attached file didn't belong with power query, Does it? If is after power query then where is queries ??
Data - Show queries
Query is loaded as connection only not as table to the worksheet
Oh gosh I didn't notice that you have hide the one sheet
I didn't hide anything
ok, step by step
1. click on source table
2. Data - From table
3. Now you've table in PQ
4. select Date column (I changed it to date not date and time
5. Unpivot other columns
6. Close and load as connection
7. Insert - Pivot Table (use external data source - select connection)
8. create PT
another way without PivotTable
- A1:F13 - source data
- J1:L1 - headers (created manually)
- J2: =OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/5,1),0)
- K2: =CHOOSE(MOD(ROW(A2)-ROW($A$2),5)+1,"Product A","Product B","Product C","Product D","Product E")
- L2: =OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/5,1),MOD(ROW(A2)-ROW($A$2),5))
- drag all formulas down to row (12*5)+1 ==>61 (in this case)
- click anywhere inside new data
- INSERT - Table
- right click on table - select Table - Total row
- filter third column (un-tick 0)
- then filter by Date as you wish
- of course format Date column as Date and Price column as Currency
(see attached file)
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
Last edited by sandy666; 08-29-2017 at 08:44 AM. Reason: file added
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks