Pivot Tables are awesome! If you are an avid pivot table user, try to crack the nut for 3 questions. Ready ?
Assume 2 Tables
The data is pretty straight forward..
- The Product ID Column is common between 2 tables
- Since all Product prices are in dollars, consider an exchange rate of 66 Rupees for 1 Dollar
3 Question to be answered and output expected
- Calculate Monthly Commission Payout. Note that commission is only paid on sales done via “Affiliates” channel
- Show Maximum Selling Product Name and its Sales month wise
- How many high selling days were there in each month. High Selling Day = Total Sales of a Day > $100. (For eg. 6 Jan 2011 was a high selling day because the total sales were more than $100)
Note that these are pivot tables I created using PowerPivot, they aren’t some patchy formulas that I have put outside the pivot 😉
Question 1 Output
Question 2 Output
Question 3 Output
How would YOU solve them ?
Power Pivot Solution