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

RSS 30 Comments…

 Share your views
  1. Hi Ashish,
    I need you help to create a measure in power pivot which calculate discount by multiple condition.

    I have three tables Sales, Town Classification and Brand wise discount by town classification.
    Sample file is attached for reference. I want to save repetition of data in column very less because I have very big sales database which size is 2.7 GB. Please help me to calculate it.

    File link is as following:
    https://www.dropbox.com/s/861u9w5ncmpy464/JSBCL%20Sales%20Dump%20Spend%20calc.xlsx?dl=0

  2. Thanks for reply....
    Can I prepare this report in 64 bit system. Is it support on 32 bit system or not?

  3. Thanks for your reply.
    I have made it, but I found an issue. This report contains multiple states report, so user can also see the other state reports which I don’t want.
    To solving this issue, I have created a master slicer sheet and protect it by password. And rest all sheets reports haven’t state filter / slicer option. Now user can only selected state report which I have selected. But this is a drawback, if any user click on Show Field List and drag and drop the state to the pivot report then he can see all the state data. To disable this I have used VBA codes.
    Sub RestrictPivotTable()
    Dim pf As PivotField
    On Error Resume Next

    With ActiveSheet.PivotTables(1)
    .EnableDrilldown = False
    .EnableFieldList = False
    .EnableFieldDialog = False
    .PivotCache.EnableRefresh = False
    For Each pf In .PivotFields
    With pf
    .DragToPage = False
    .DragToRow = False
    .DragToColumn = False
    .DragToData = False
    .DragToHide = False
    End With
    Next pf
    End With

    End Sub

    But this codes aren’t works on PowerPivot filed list.
    Can you help me to disable / restrict the power pivot filed list?

  4. Hi Ashish,
    How can I calculate the number of outlets who purchased a brands in each month through Power Pivot?
    I given a link for sample database.

    Link - https://onedrive.live.com/redir?page=view&resid=354CE60AA62E9D4C!172&authkey=!ACbrg-9HywTn8Lg

  5. Hi Ashish,
    Thanks for your support.
    It is working absolutely great, but I have a query when I am adding further month data in my database. It is still showing number of outlet billed three times. I have to manually to change in the formula.

    How can I automate this.

    I have another Queries
    1. How can I see numbers of outlet billed in last three months
    2. How can i calculate the repeat outlet billed numbers for each month

  6. aditya pratap singh November 30, 2015 at 4:27 pm

    Hi Ashish,

    I wanted to know how to change the coloumn label from A,B,C,D,E,... to some other words and also I have seen in some tables as we go down the header of the table beacomes coloumn label

    Can you please help me in finding right solution.

  7. Hi Ashish, I need your help to increase the performance of a Big Power Pivot model which have 13 millions row in Sales Master table and there are 30 more supporting table. Now it became a giant. It is taking more calculating time like 40 -50 seconds for some measures and effecting system performance.

    I want to restructure it to get better performance. I have some questions in my mind before i restart the restructure.

    Question 1 - Is create a calculated columns increase performance against already stored data columns. For example, in my sales table I have Product code and its segment code. Is replacing segment code column from static to calculated column can increase its performance.

    • Hi,

      I always prefer using calculated columns in the Data model (PowerPivot window) to using spare columns for calculation in the base data itself. This reduces the file size and increases performance.

  8. none of your file is downloadable right now. isit just me or there is problem with sharepoint

  9. 1.       How can I make the segmentation mapping based on multiple criteria without adding helping columns in main data?
    I have a complex brand segmentation system which divide brands based on State and Business type.
    For example, a brand "BRND0001" for State code "STA_BHR" and Business Type "DOM" is in Segment "SEG1", but for the Business Type “INTL” it is in SEG2.

    I use to solve these kind of issues by creating the helping column which I currently done in the exiting power pivot model. But I have a very big database of 13 million records and by adding those helping columns it is increasing my file size.

    2.       How can I calculate scheme in the given model?
    I have a scheme table for the secondary customers which disburse them after billing. For example in the scheme sheet 1st table row we have given Rs. 100 for each quantity of brand “BRND0001” which is purchased by the customer “CSTR0001” for the Period “201401”.

    I used sumx for calculate the scheme amount, but it too slow down my model so I removed it.

    Please help.

    File Link - https://onedrive.live.com/redir?page=view&resid=354CE60AA62E9D4C!175&authkey=!ADfL7ccsXV0b4vs

    • Hi,

      Simply stated, you want to know how you create your existing Pivot Table without using the 6,7 and 8th column in the Data Model (you want to avoid those RELATED() functions which you have writtne). Since you have already established relationships among all relevant columns, you need not write the RELATED() functions at all.

      Create a Pivot Table like this

      1. Drag Segment from the Segment Table to the Row labels
      2. Drag Brand Code from the Product Table to the Row labels
      3. Drag State code from the PrimaryCustomer Table to the Column labels
      4. Drag Sale (the calculated Field formula) to the Value area section

      The result will be the same.

      I do not understand your second question. Please share your expected result.

  10. Hi,
    Can you help me to calculate the unique outlets who are doing repeat purchase with their number of time purchase frequencies by Brand through Power Pivot DAX measure?

    For example, Brand B was purchased 15 times by 34 outlets with the quantity of 56500 whereas Brand A was didn’t bought 15 times by any outlets.
    Second example, Brand A was purchased by 14 times by 4 outlets whereas Brand B was purchased by 46 Outlets.
    In the below link, I have given the Sample Data set and the result required sheet.

    https://1drv.ms/x/s!AkydLqYK5kw1gTJ8DBsKbBXblwef

    thanks

  11. Hi
    yes you are right. it is basically month count
    Thanks for replying, but i am not able to download the workbook from the above
    link.

    Can you give me some other link.

  12. Hi Ashish,
    Data tab have only three options. "Refresh Selected Connection", "Refresh All Connections" and "Calculate Workbook".

    there is no any download option. I request you to please mail me the solution on ankur.marco@gmail.com

  13. Hi Ashish,
    I have two different Power Pivot model files. One have Target Vs Achievement of the employees by brand, by month & etc and other one have there execution scores like number of visits on the outlets, display scores of outlets, outlets stock index and etc.

    I have to merge those models into one to get the consolidated report. Is there any process which help me to merge both power pivot models into one pivot model. Either than i have to work on entire power pivot models to create their design , database & measure which very big task.

Leave a Comment

Your email address will not be published.

*

*