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),"")

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!

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.

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!

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),"")

Hi Catalin,

Ohhh...so stupid me..haha..i am thinking too complicated.Between,its just so simple 😀

Yes you are right, just grab a label only..omg..i am thinking out of area..

Really dont know how to thanks you again...thank you very much!!

Regards,

Hooi Thin

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!

I think you can simply apply a Top 10 filter in cell O7 filter (Total), or in the other columns: L7 or M7, N7.

Record a macro when applying the filter, and add buttons to run the filter codes.

This would be the most simple approach, based on your structure.

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?

You will obviously handle that in code. When a quarter is selected, use .ShowAllData before applying the top 10 filter on that column, there is no need to have buttons to unfilter, then to filter.

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

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:

Apply your code to filter where the comment in code says.

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.

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

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~