Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi,
This is your formula:
=IFERROR(GETPIVOTDATA("Total Cost",$A$6,"Fiscal Quarter","2016 Q 1","SRAccountName",$A$8:$A$100)/GETPIVOTDATA("USD Amount",$E$6,"Fiscal Quarter","2016 Q 1","Customer Name",$E$8:$E$100),"")
The Formula should indicate cells, not entire ranges, it should be:
=IFERROR(GETPIVOTDATA("Total Cost",$A$6,"Fiscal Quarter","2016 Q 1","SRAccountName",$A$8)/GETPIVOTDATA("USD Amount",$E$6,"Fiscal Quarter","2016 Q 1","Customer Name",$E$8),"")
July 11, 2016
Hi Catalin,
Glad to receive your reply again ๐
The formula you mentioned here is fixed the cell reference without moving.It should be $A8 instead? My example is just showing one row on part usage but the real data consist of thousand rows.
=IFERROR(GETPIVOTDATA("Total Cost",$A$6,"Fiscal Quarter","2016 Q 1","SRAccountName",$A$8)/GETPIVOTDATA("USD Amount",$E$6,"Fiscal Quarter","2016 Q 1","Customer Name",$E$8),"")
But the main issue is the row label for Part Usage table and Revenue table will keep changing based on the filter.
For example on the attachment even though if i use $A8 drag down to $A15(part usage table) on Q1 and $E8 drag down to $E15 (revenue table) on Q1, if i filter the field to agreement,the next correct position for customer FFF should be B11/F13 but the current formula was wrong,it grab A11/E11.
How do i grab the row and column position correctly if the cells are not fixed?
Thanks again!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
I assumed that you know how to handle absolute and relative range references. Here is a link that may help you understand that:
excel-2007-absolute-references
Don't understand what your problem is. Yes, when you apply filters on Part Usage pivot, the list of customers from range K14:K25 will change too. What is it you want? Maybe it's better if you can describe more clearly what you want, even prepare a manual result on how it should look.
July 11, 2016
Hi Catalin,
Yes.I know how the absolute and relative reference work.
Ok sure i can explain more details on the attachment. The result table is follow exactly whichย display on part usage table,the list of customers will change too.
More simpler way to explain is no matter how the rows and columns is changing for the two tables after filtered, i want to match the same customer and same quarter between the two table to perform the division calculation.
For this case, the quarters are just few items,but customer list is having around thousand rows.The problem is how to reference the cells if the rows keep changing?
Attachment whichย displayedย the different colours to show that the correct item to grab between the two tables, hope it can helps more clear.
Let me know if anything i need to add on.
Thanks Catalin!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
I see what you mean.
I have no idea why you are chasing around cells ๐
Why don't you use the same name from the same table, in both GETPIVOT formulas?
=IFERROR(GETPIVOTDATA("Total Cost",$A$6,"Fiscal Quarter","2016 Q 3","SRAccountName",$A11)/GETPIVOTDATA("USD Amount",$E$6,"Fiscal Quarter","2016 Q 3","Customer Name",$A11),"")
More, you dont even have to refer to column A names from that pivot table, you have the list closer, in the same row, column J, use it in both formulas!
=IFERROR(GETPIVOTDATA("Total Cost",$A$6,"Fiscal Quarter","2016 Q 3","SRAccountName",$J18)/GETPIVOTDATA("USD Amount",$E$6,"Fiscal Quarter","2016 Q 3","Customer Name",$J18),"")
July 11, 2016
Hi Catalin,
I still have a question to ask for your opinion regarding the pivot table, can i do a slicer to sort top 10? I am looking for tutorial for creating own slicer on sorting this but no able to get it. Can you guide me?
As attachment,I need to use slicer to filter quarter and then sort top 10 (to see which customer having the most part usage% on the certain quarter).I am looking for other option other than manually filter top 10 on the number filter(total column).
For this case, i created a new column (sum of total column) so that if user select Q1 and Q2, they both can be sum up and filter among the top 10.ย
What i want is the top 10 row will keep changing customer based on the selection quarter(either one quarter or multiple quarter). Each quarter stick with top 10 customers.
The table formula i am using is getpivotdata, perhaps i should write the getpivotdata in power query or somewhere else? and display only the top 10 value by quarter after click the slicer eg :2016 Q1? Which way is better?
I have go through many studies about this, and most people are using power pivot and RANKXย or TopN formula to do it.But other than this, have any other option? I am not familiar to this advance formula, so need your great help.
Hope you can helps! Thanks!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
July 11, 2016
Hi Catalin,
Yes it is more simple to do with macro, but what if to clear the filter of top 10?
I would like to do like which have the clear filter icon too to clear out the top 10 based on user's need same like the features of slicer.
Actually i am quite suspected what i want to do is correct logic. How do i actually make the correct logic of the filter? If the user did not clear out the top 10 filter, and continue to filter the next selection of the quarter period and they will not get the correct top 10 value.
Unless filter 2016 Q1, then top 10. Unfiltered top 10 ,continue select 2016 Q2 and to sort top 10 again and again...it seem like not user friendly..
For this case, not sure have other alternative method to deal with this top 10 filter?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
July 11, 2016
Hi Catalin,
Thanks for the feedback.
I am able to create a button to sort the top 10.Now stuck on the unfilter issue.
But the quarter selected is on slicer, possible to assign macro to slicer?Because i tested to assign macro to the quarter slicer, it stops working as a slicer anymore.ย What is the macro event to associate with slicer?
I tried usingย worksheet's PivotTableUpdate event and it did not works as well. Can you correct me?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim slBox As SlicerCache
Dim slItem As SlicerItem
Set slBox = ActiveWorkbook.SlicerCaches(Slicer_Fiscal_Quarter) 'using slicer formula name
For Each slItem In slBox.SlicerItems
Sheets("Pivot Table").Range("O7").ShowAllData
Next slItem
End Sub
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
A slicer does not have events, pivot tables can have an event associated with an action.
You can loop through slicer elements to see which one is selected, this way you will know where to apply the filter.
Try:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotTable4" Then
ย ย ย Dim slBox As SlicerCache
ย ย ย Dim slItem As SlicerItem
ย ย ย Set slBox = ActiveWorkbook.SlicerCaches("Slicer_Fiscal_Quarter") 'using slicer formula name
ย ย ย
ย ย ย Application.EnableEvents = False
ย ย ย For Each slItem In slBox.SlicerItems
ย ย ย ย ย ย ย If slItem.Selected = True Then
ย ย ย ย ย ย ย ย ย ย ย MsgBox slItem.Name
ย ย ย ย ย ย ย ย ย ย ย 'apply the filter for this slitem
ย ย ย ย ย ย ย ย ย ย ย Exit For
ย ย ย ย ย ย ย End If
ย ย ย Next slItem
ย ย ย Application.EnableEvents = True
End If
End Sub
ย
Apply your code to filter where the comment in code says.
July 11, 2016
Hi Catalin,
I tried to use your code, but when i try to debug,it doesn't fire the Worksheet_PivotTableUpdate event? Not able to step into the code and testing whether it can detect the slicer items. I am not sure which one is wrong.
Attached the fileย with the vba code for your reference. Hope you help.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
The code should be in Sheet Pivot Table vba module, not in a regular module, there are sheet level events that can be triggered, and Workbook level events.
Sheet events should be written in sheet modules, workbook events in ThisWorkbook module.
More info here: workbook-worksheet-events-excel-vba
July 11, 2016
Hi Catalin,
Sorry that i keep try and try but also no success, really need your help ๐
I moved the code from normal module to the sheet2 (pivot table),at 1st i changed toย Worksheet_PivotTableChangeSync event, it can prompt the messagebox mean the event can work.But after awhile i tried again it is not working anymore and i cannot figure out why.
The .ShowAllData method i putย Sheets("Pivot Table").ShowAllData,its not working. I tried with ActiveSheet.ShowAllData also cannot.
What is the correct code i should write?
Sorry for interrupt again.Appreciate again if you can help~
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Try the attached file.
I defined a name for cell K7 in Pivot Table sheet (FirstCell) , this name is used in code. This will help identify in code the filtered range in a more flexible manner.
Make sure that the filtered range is surrounded by empty cells: for example, K6:O6 should be empty, cells from P7 down, from J6 down.
If you type anything in cell K6 for example, the code will fail. The code is using CurrentRegion to identify the range, and the first rows with headers, it will identify the column to be filtered based on the selected Quarter from slicer.
July 11, 2016
Hi Catalin,
Wow,the code is really awesome! I learned something new from you ๐ Thank you!
Do you have any vba course available that can learn for this website? I would like to build up the vba skill also, now i know the vba world is so big ๐
But compare to Power Query,which one is more high demand on the market? I know both of them as two different powerful tool, probably learn both is the best, but kind of budget issue, i wish to choose the most suitable course to learn that can build up my excel skill so that can survive in this competitive environment.
Between, the code works for me, can i just asking one more question i am not sure this concept can be work?
If the top 10 list after select the quarter from slicer, eg :ZZZ,BBB,CCC,JJJ,WWW,QQQ,AAA,XXX,CCC,FFF
The table part usage and revenue as attachment, they are located horizontally together with the top 10 list. I can't make it vertically as there are too many rows in my real data.
If top 10 are sorted, the table part usage and revenue for their rows will be running away if i am having the real data consist with thousand rows.
Can i make the table part usage and revenue also appear the same value as top 10 list, would it possible? Because user after viewed the top 10 list and might want to go back to the two tables to look for the actual value.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Well, Power Query can be very useful, it can be compared with vba in terms of getting the same results in different ways. The course is available on our site. The beauty of Power Query is that it can be mastered in a much shorter time than vba, and the benefits are immediate.
VBA will require years of practice after finishing the basic course, or even a more advanced course, if you're not an experienced programmer, with already tuned programming skills from other languages. We don't have a vba course yet, but we can recommend one from our friends and partners.
Will take a look at the last problems, as soon as possible.
July 11, 2016
Hi Catalin,
Yes i see VBA is abit complicated than Power Query.
I have programming background and currently dealing with asp.net and vba project, just wish to enhance more knowledge other than asp language.Which vba course from your friend to recommend? I wish to research more info before taking those course.Recently i had watched webinar from Jon at Excel Campusย http://www.excelcampus.com/enroll/. Not sure does it worth to take the course?
Awaiting your soonest reply for the top 10 question ๐
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Of course, you can try Jon's course. Also, Mynda recommended few weeks ago Allen's course: the Excel Macros for Beginners course
Maybe he has more levels than beginners, choose what suites you.
About your top 10:
I find your way of doing things very confusing. Few ages ago (or about 100 messages ago ๐ ), you agreed that there is no relationship between those 2 tables, but you are still trying to combine data from those sources, in some calculations. If you decided to combine them, why don't you use the solution you had for merging the data with power query? If you do that, you will have all the data in a single table, in a single report, no need for twisted and complicated solutions. Everything will be simpler as it should be. If I remember well, you have a solution with that calculation inside power query, no formulas needed, just apply the top 10 and that's all.
Your existing setup is not right, because when you apply a filter in the percentage range, rows from first 2 tables will also be hidden, and that should not happen.
1 Guest(s)