Pivot Tables are awesome! If you are an avid pivot table user, try to crack the nut for 3 questions. Ready ?

Assume 2 Tables

Pivot table questions 1

Pivot table questions 2

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

Pivot table questions 3

 

Question 2 Output

Pivot table questions 4

 

Question 3 Output

Pivot table questions 5

 

How would YOU solve them ?

DOWNLOAD THE EXCEL FILE

 

Excel Solution

DOWNLOAD EXCEL SOLUTION

 

Power Pivot Solution

DOWNLOAD POWER PIVOT SOLUTION

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI