Pivot Table Questions

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..

1. The Product ID Column is common between 2 tables
2. 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

1. Calculate Monthly Commission Payout. Note that commission is only paid on sales done via “Affiliates” channel
2. Show Maximum Selling Product Name and its Sales month wise
3. 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 ?

DOWNLOAD THE EXCEL FILE

 

Excel Solution

 

DOWNLOAD EXCEL SOLUTION

Power Pivot Solution

 

DOWNLOAD POWER PIVOT SOLUTION