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 used the grouping from the attached image.
I do not know which is the correct grouping, you should know that. In the Customer tab, you have percentages by quarter and platform, and another percentage depending on quarter, platform and client. To which one are you referring?
If the grouping by country is not necessary, just remove it from that step.
July 11, 2016
The quarter and platform table which are not pivot table is just created for counting the correct percentage only, will delete after that.
I need the pivot selected from customer and country and then to get the result of quarter and platform.
Actually now i am confusing with the matching columns and the group by, by right i should group by customer,country,quarter and platform, but still can't get the result i want 🙁
Now i changed to matching all columns except Net amount USD-Acc Rate, and group by all columns except Net amount USD-Acc Rate.
And then i go to check on the source in small subset of the filtered with customer GGG and quarter 1 with AXO,the sum value of ordercost column is correct for 32135 on powerquery tab,but for partscost is not tally which the correct value should be 16852,but there only 7901?
The partsCost should be 20 rows total but it just show only 2 rows follow with order's source (also 2 rows).Because the group by multiple selection is based on the orders table to filter so it always will take the total row filtered by orders table. So is the grouping columns issue?
https://drive.google.com/open?.....0lzNjVBbWs
One more thing, i miss up an important point 🙁 How do Order and Parts pivot control percentage table? if i do not want to show the filters(dropdown)from field list on percentage table , just show the quarter and platform. Then control by order and parts dropdown then the percentage table will follow to change the value.
Do appreciate Catalin for always supporting me, and this is the last step almost to complete my project, really hope you can advise me the perfect solution.
Many thanks!
Regards,
Hooi Thin
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
In the attached file, I merged the 2 tables by 4 keys: Customer Name, Country, Platform and Quarter. This is the equivalent of performing a Vlookup based on 4 criterias instead of one. If there was a unique ID column, then that was enough for merging the data. I displayed only the Acct Rate from the Parts table, the other 4 columns are already displayed in the Orders table, in the merged query.
Grouping the data is different, it's just a way to aggregate data, into relevant categories. I used for grouping the same 4 columns that were used as keys in the merge, I also added a custom formula to divide parts amount by orders amount.
From this point, you can display pivot table reports using the result of the query as data source, you can display Orders amount, parts amount and percentage from the same query (we have all the data now into one table, remember that's the reason we merged the tables, to be able to perform calculations and reports)
About the data issues:
the errors are coming from your sample data: check rows 3 to 15, in orders and parts sources. Looks like the platform (column I) is different for the same Sales Order number (column H), which is unusual for me. It seems like a source data problem, you should know better than me, it's your data. If that is correct, maybe you should not use the platform as a key for merging and grouping...
Hope it helps.
July 11, 2016
I am sorry Catalin, my mistake to give you the wrong format of the columns source of the two tables which misconfused the concept already.
Now i reattached another sample(follow my live project format) for your reference.The two tables supposingly is having different columns,they both are totally not connected to each other, they do not have unique ID column, the 1st ID column is i generated myself.
Both of the tables having the 4 same key (Customer Name, Country, Platform and Quarter).Just they are having different header name only,so can't merge?
As i know, there is no way to connect 2 different data sets to the same slicer,right? If like this, not sure which other better method?
For your information,before i doing these two pivot tables,i tried on hard time to connect with slicer on the two different data sets, but in the end i give up so i only doing it manually with pivot tables.
If this case, the merge method still can work? As both of the tables's header name are not same although having the same source columns.
Apologize for mess up and thanks for your patience, hopefully this sample can make the situation on the correct path.
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
In parts table, you have 3 countries: China, France, United States.
In orders table, you have 5 countries: France, Singapore, Taiwan, Mexico, Switzerland.
There will be no matches based on those 4 criterias for merge, the data is still not right. The headers names are irrelevant, the data in those columns must be similar. You can match columns with different headers, as long they have similar data. All you have to do is to test it, it will work even if you have different headers names. The last file i sent is the way it should look like: after you merge the tables, you can use only 1 slicer to control each pivot table made from the merged query, you don't have to make separate pivot tables from each source table. Once you merged the source tables, you will have only 1 source, that will include both tables. All pivot tables made from the merged table will have the same cache, they can be controlled with the same slicer.
July 11, 2016
Yes.My original data source already is different data sets, that's why the both tables are totally not connected to each other.
The real data on my project is 25 country(orders),87386 rows vs 41 country(parts),19005 rows. They have many different criterias are not similar too. For example, country Brazil in orders table,having customer A,B,C but in parts table may have only customer D or customer A,C.
I can not make any changes to the raw file to make them similar data.
Just wonder if this is the case, which is the suitable method to solve the issue? Probably the merging method is not working for this case?
If can work with the slicer it would be a perfect way, as i really wish everything can be done by slicer. But for my case so complicated, not sure how?
Do appreciate again if 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
If there is no relationship between the tables, then what are we talking about 🙂 ? the Parts/Orders division does not make sense in this case.
You can still merge them, and create pivot tables to display the Net Amounts for parts and orders, the pivot tables will have the same source ( the merged table) and they can be controlled with the same slicers.
July 11, 2016
There are no relationship between the tables, but the users want to compare the results of parts table and the orders table for the 4 categories(Customer Name, Country, Platform and Quarter) even though they are not connected to each other. They want to analyse during the period the percentage of the certain product by Parts/Orders.
You mean not make sense is it this case? Happened with Parts table have Customer A, but Orders table do not have Customer A? Then for this row data value is empty.Each of a table having the data that another table don't have.
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
You can Append table2 data to table 1 data, check the attached file. In each query, I added a custom column to indicate the Data Source (orders-Parts),
then in the pivot tables I added a report filter to show in the first pivot table only data from Orders, and in the second pivot only data from Parts. This way, they have the same pivot cache and they can be controlled with the same slicers.
For your formulas, all you have to use is GetPivotData formula, see this article: excel-getpivotdata-function
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
Did you checked the formula from cell G2?
There are no cell references other than $A$17 and $A$5, and these are the pivot tables first cells (a pivot table location is determined by its first cell position)
=GETPIVOTDATA("Net Amt USD - Acct Rate",$A$17,"Order Line Trans Quarter","2015 Q 1","Platform","AXO ")
The code for the Platform is typed into formula (same for Quarter), you can replace it with a cell reference, if you need to. But all these are explained in the link I sent you, I have the strong feeling that I'm talking to myself, you did not read the article from the link I sent about GETPIVOTDATA, right? 🙂
Take your time to analize the links I sent, all you need to know is there.
Cheers,
Catalin
July 11, 2016
Hi Catalin,
I did read the article, and tried with the example, that time i was not so understand what i am doing, i do not notice that i did not change the position name, and keep assume that was not working for my case, really so bad 🙁 so sorry~
Now i able to made it work by changing the two variables changing according by correct cell reference. Really tons of thank you for this formula, after many rounds of hard work, finally i know how to use this formula well, really glad that this issue solved, been struggling for this so long time, and it just solved by this simple formula only 🙂
Before come to an end, and it almost to an end, i am still having a question on refreshing data issue,
How to refresh pivot table in a protected sheet?Other than manually unprotect, refresh and re-protect. I saw some of people using VBA and assign macro to the button.But i do not want to create a button to let users refresh them. I will refresh on my side and send them the file. Which method can make it other than VBA?
Many 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
July 11, 2016
Hi Catalin,
I tried to put below code on vba “ThisWorkbook” page, but do not know how to assign the macro.Must assign this macro to a button?
Can i make it like when opening the tab of worksheet, then the pivot tables on the active sheet will auto refresh by the macro code?
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim pt As PivotTable, bPiv As Boolean
If Sh.Type <> xlWorksheet Then Exit Sub
For Each pt In Me.PivotTables
Me.Protect Password:="1234", UserInterfaceOnly:=True
pt.RefreshTable
Next pt
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
The code you posted is just protecting the sheet, without unprotecting the sheet before refresh. More, the code is protecting the sheet before refreshing the pivot :), that is weird. The corect sequence is unprotecting the sheet, refresh all pivot tables, then protect the sheet back. Try this:
Dim pt As PivotTable
If Sh.Type <> xlWorksheet Then Exit Sub
Sh.UnProtect Password:="1234"
For Each pt In Sh.PivotTables
pt.RefreshTable
Next pt
Sh.Protect Password:="1234", UserInterfaceOnly:=True
End Sub
July 11, 2016
Hi Catalin,
The code i just copied from other's online solution, do not know the logic was not correct 😛 Thanks for your code.
1)As i know if want to assign a macro have to use Sub Functionname()? If using Private Sub can assign? Many times i saw a private sub function code but i do not know how to use it.
2)Locate vba code inside "ThisWorkbook" means it will run the macro in every sheets when i open the file? If i put in module it should be ok also right?
3)And is it a must to assign to a button in order to run this macro?So far i only know to assign macro to a button on module page.
I do not want to allow users to click the refresh button. I am thinking to assign macro to a button on a new sheet and make the worksheet hidden, do you think is a proper way to make it?
Seeking for your opinion, 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
You should add the code to the personal.xlsb workbook, and add an icon to your toolbar, with that code, this way the code will be available only on your computer.
Here is a link that will teach you how to do this: create-a-personal-macro-workbook-personal-xlsb
The code should be:
Sub RefreshPT()
Dim pt As PivotTable
ActiveSheet.Unprotect Password:="1234"
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
ActiveSheet.Protect Password:="1234", UserInterfaceOnly:=True
End Sub
July 11, 2016
Wow..bravo function! It works like charm.Very nice tutorial, i learned a lot throughout your teaching and those links you provided has upgraded my skills at my work pace.
Really really thousand of thanks to you Catalin,for your patient to guide me along the way to complete my project well now,do appreciate your helps very much, after this i am interested to join your online course, but kind of budget issue, i will consider to take the course in future. 🙂
Once again, thank you very much! I will recommend online training hub to others 🙂
May you always be well and happy 🙂
Best Regards,
Hooi Thin
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,
I am back to here again to seek for your help as i am facing the issue for the GETPIVOTDATA that you teached me last time.
Last time my case was using the formula which grab the row label and column label to get the correct position with the data.
for example =GETPIVOTDATA("Net Amt USD - Acct Rate",$A$17,"Order Line Trans Quarter","2015 Q 1","Platform","AXO ")
But now i am doing slightly complicated ,after the filtering, the column label i still can get the quarter name ,but row label it was not fixed, as it have thousand customers and it always change accordingly.
For this case, how i going to grab the correct value of the position? If using cell reference it can get the correct value but appear with the position issue(please refer to my attachment)
Appreciate if you can help me again.Thanks!
Regards,
Hooi Thin
1 Guest(s)