Tags: CUBERANKEDMEMBER

Sort individual columns of a Pivot Table based on a slicer selection

{0 Comments}

Here is a simple four column dataset

Week Team User Codes
1009-1016 Default-LossMit FAST INTL\KOrdillano ATPD/5
1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/116
1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/3B
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116
1009-1016 Direct - HSD INTL\JCustodioii S/2
1009-1016 Default-LossMit FAST INTL\abacud ATWI/116
1009-1016 Default-LossMit FAST INTL\SCaparon ATWI/116
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116
1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116

A simple Pivot Table (with a slicer) created from this dataset looks like this

Untitled

The objective is to determine the Top 3 users of each week for each slicer selection.  Unfortunately, there is no way to sort multiple columns of a Pivot Table all at once.  Once may either sort by the Grand Total column or by the individual week wise columns.  Since we do not want to sort by the Grand Total column, the only way out is to sort the individual week wise columns.  The expected result should look like this:

Untitled

I have solved this problem by using CUBE formulas.  You may refer to my solution in this workbook.

Sales data modelling and interactive visualisations

{30 Comments}

Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code.  In this sales dataset, each line item represents one visit for one SKU.  If nothing is sold in a certain visit, then the SKU column displays No Sale.  So effectively there is a line item for each visit whether or not something is sold in that visit.

From this simple Sales dataset, here are a few questions which one may need to find answers to:

1. How did the Company perform (in both years 2013 and 2014) on two of the most critical Key Performance Indicators (KPI's) - Quantity sold and Number of Visits.  Also, what is the month wise break up of these two KPI's.

2. Study and slice the two KPI's from various perspectives such as "Type of Outlet visited", "Type of Visit" - Scheduled or Unscheduled, "Day of week", "Brand", "Sub brand".

3. Over a period of time, how did various SKU's fair on the twin planks of "Effort" i.e. Number of visits YTD and "Business Generated" i.e. Quantity sold YTD.

4. Analyse the performance of the Company on both KPI's:
a. During Festive season/Promotional periods/Events; and
b. During different months of the same year; and
c. During same month of different years; and
d. Quarter to Date

5. "Complimentary Product sold Analysis" - Analysis displayed on online retailers such as Amazon.com - "Customers who bought this also bought this".  So in the Sales dataset referred to above, one may want to know "In this month, outlets which bought this SKU, also bought this much quantity of these other SKU's."

6. "Outlet Rank slippage" - Which are the Top 10 Outlets in 2013 and what rank did they maintain in 2014.  What is the proportion of quantity sold by each of the Top 10 outlets of 2013 to:
a. Total quantity sold by all Top 10 outlets in 2013; and
b. Total quantity sold by all outlets in 2013

7. In any selected month, which new outlets did the Company forge partnerships with

8. Which employees visited their assigned outlets once in two or three weeks instead of visiting them once every week (as required by Management).

9. Which outlets were not visited at all in a particular month

10. Business generated from loyal Customers - Loyal Customers are those who transacted with the Company in a chosen month and in the previous 2 months.

These are only a few of my favourite questions which I needed answers to when I first reviewed this Sales Data.  Using Microsoft Excel's Business Intelligence Tools (Power Query, PowerPivot and Power View), I could answer all questions stated above and a lot more.

You may watch a short video of my solution here

Show Slicer selection on a Graph

{6 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  Now let's say, a user makes a few slicer selections on the Pivot Table worksheet.  When one now clicks on the Pivot Chart worksheet, one does not see what selections were made in the slicers (which are placed on the Pivot Table worksheet).  So one has to go back and forth between the two worksheets to keep track of the slicer selections made.  One may want to view the slicer selections made on the Pivot Chart as well.  Changes made in the slicer selections should automatically reflect on the Pivot Chart worksheet.

This can be accomplished by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here