I have a question regarding to the pivot table: 1 have data base for the actual sales by every sales person in different country, I also have another data sheet to show each sales person's monthly sales forecast.
My proposal is:
1. Combine the 2 data sheets together in a pivot table which could be showed by every country and by every sales person by month: the first line is heading about every column's description, the second line named "actual sales" to show the actual sales quantity, the third line names "sales forecast" is showed the sales forecast, the fourth line named "day% passed" could be showed how many days passed in the current month by%(in the past month could be showed the 100% passed, just set a same format), the fifth line named "% sales achievement" could be showed the actual sales vs. sales forecast%
2. The first column in the Pivot table is Product description, the second column is per sales person name under country. Starting from the third column, is the monthly sales numbers.
3. When finish the Pivot table, what I would do is update the actual sales quantity and the coming months sales forecast, then refresh the Pivot table, the % of every sales achievement could be updated at the same time.
Thank you very much,
Lucy
Hi Lucy,
It's very difficult to visualize your complex scenario without a sample Excel file. That said, you could (maybe) use Power Pivot to create relationships between your tables so you could create a single PivotTable that pulls data from both tables.
Power Pivot is more complex than regular PivotTables and requires an understanding of data modelling. Also, Power Pivot is only available in some versions of Excel:
Versions of Excel supporting Power Pivot: https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b
If you can provide a sample file and show us how you want the data consolidated in a mock-up then we can provide a more specific answer. Also advise what version of Excel you have.
Thanks,
Mynda
Hi Mynda,
The "PT" sheet in the attachment is the sample I want to have. Just want to know how to use the 2 sheets combine into 1 PT, and the day pass% could be refreshed everyday when I open the file. Then sheets of "actual sales" and "sales forecast" is a sample only. What I will do in the real work is to link the same file from the SAP system, that could be refreshed as well directly from SAP database. Thus, the current month actual sales could be changed every day after refreshed. This PT is very helpful to sales manage to monitor his team's progress every day.
Very appreciated if you can help,
Thank you very much,
Lucy
Hi Lucy,
No file attached. Please click the 'Start Upload' button to complete the upload of your file.
Also, what version of Excel do you have and is is Professional Plus, Home & Student etc.
Mynda
Hi Mynda,
Sorry about that. Now I attach the file to you.
Thank you very much,
Lucy
Hi Lucy,
Thanks for sharing your file. Please tell me what version of Excel you have and is it Professional Plus, Home & Student etc.
I need to know if you have a version compatible with Power Query and or Power Pivot before I can offer you a solution. There's no point in me giving you a Power Pivot solution if your version of Excel doesn't have Power Pivot.
Thanks,
Mynda
Hi Lucy
You can also try using PivotTable as per attached.
Not too sure how you calculate the Day Pass % but my guess is today's date less end of last month's date.
Can't help you on the PQ or PP (no idea how to use them). Mynda can help you on that.
Good luck.
Sunny
Hi Mynda,
It is Excel 2016, Window 7 Professional.
The stupid way I can only do now is copy and paste as value the PT to another sheet in the same file, then insert the two lines for "day pass %" and "sales achievement %" 
then set the simple formula to get the %.
Thank you very very much,
Lucy
Hi Sunny,
Thanks for your sharing, it almost looks like what I want
I can calculate how many days pass in this month with the formula as: =DAY(TODAY())/DAY(EOMONTH(TODAY(), 1)). For example, today is Oct 20th, 20 days past, Oct has 31 days, then 20/31=67%.
May I have some questions to you?
How did you combine the sheets of "actual sales" and "sales forecast" into one PT?
There is no any column in the 2 sheets as "day pass%" and "sales achievement%", how did you make it to show in PT?
Thanks for your help,
Lucy
Hi Lucy
I have included many notes in the worksheet itself to explain what I am doing.
Q1. How did you combine the sheets of "actual sales" and "sales forecast" into one PT?
A1. I use the SUMIFS() function.
Q2. There is no any column in the 2 sheets as "day pass%" and "sales achievement%", how did you make it to show in PT?
A2. I use the PT Calculated fields to create them.
I have recreated the PivotTable now that I know how you created the Day Pass %.
It is a better version that the previous one as I am now using helper columns instead of calculated fields.
Hope this helps.
Sunny
Hi Mynda,
My legtop is Microsoft Office Home and Business 2016.
Thanks,
Lucy
Hi Lucy,
Thanks for confirming the version of Excel you have. Unfortunately Home and Business doesn't have Power Pivot, but it does have Power Query, so I've put together a Power Query solution for you. It's similar to Sunny's, but it uses Power Query to merge the Actual and Forecast data instead of formulas.
I added the Day Pass % calculation in Power Query, instead of in the Excel table. This will be more efficient if you have a lot of data because Excel isn't having to recalculate the volatile TODAY function all the time.
As with Sunny's solution, I had to fix the format of your September dates in the forecast table. I also renamed the columns in Power Query so they were consistent in both the actual and forecast tables so I could merge them.
I used a calculated field to add the Sales Achievement % in the PivotTable. You can learn more about PivotTable Calculated Fields here.
Power Query can connect to SAP HANA databases, so if your SAP database isn't that type then you can export to a CSV, Text or Excel file and Power Query can get the data from there.
Unfortunately I'm not able to teach you everything you need to know about this Power Query solution in the forum, there's just too much to cover, but you can learn Power Query here.
The benefits of Power Query is that once you set it up there's really no maintenance. All you have to do each week when you get new data is click the Refresh All button on the Data tab. Power Query will get the updated files, run the data through all the cleaning steps, copy the Day Pass % formula down and then send the data to the PivotTable which will update the report. It really should be a case of opening the file and refreshing.
I hope that gives you an idea and some inspiration. Please let me know if you have any questions.
Mynda
Hi Mynda,
It is helpful, I need time to learn more.
My Excel 2016 has Pivot tables as well. So I would compare which way would be better to run this assignment.
Thank you for your great support very much,
Lucy
Hi Lucy,
Just to clarify; all versions of Excel have PivotTables, but not all have Power Pivot. Power Pivot is a different tool that allows you to connect multiple tables together and then summarise the data in a single PivotTable. As opposed to the solutions offered above which require the data to be consolidated into a single table before summarising.
Mynda
Hi Mynda,
If there is no Power Pivot, can I use the function of "Connections" to consolidate the several table into a single table? What are their different function?
Thanks,
Lucy
