Assume the following data layout Name Budget April Actual April Budget May Actual May Budget June Actual June a 2 1 1 4 3 b 4 4 2 1 c 2 3 3 3 d 2 1 e 5 6 4 8 6 As one can observe here, there are two sub columns for each […]
Read More →
Category: POWER QUERY + POWERPIVOT
Compute potential Sales of a retail outlet
{ 8 Comments }
The objective is to assist a Store Manager with computing potential sales across different products and colours. To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]
Read More →
Assume there are four interrelated tables. One may want to create a pivot that allows one to filter data by using the slicers. Data should be filtered by the following interdependent slicers selections: Customer, Country and segment. The logic behind the pivot when using the slicers shall be as follows: 1. Feature N is only shown if relevant to Customer X in Segment […]
Read More →
Compute attrition rate from two different data sources
{ 0 Comments }
Assume two databases: 1. One showing employee headcount (one row per employee) which has all employee details such as Name, ID, Date of Joining, Supervisor name, Department etc. (Range A1:R781 of Source worksheet) 2. The other showing data for employees who resigned. (Range U1:Z36 of Source worksheet) The task is to compute the attrition rate for selected Group and […]
Read More →
Story telling with Excel Power BI
{ 6 Comments }
With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, […]
Read More →
Assume two workbooks named Book1.xls and Book2.xls. In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb. In both the worksheets – Jan and Feb, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of […]
Read More →
Create a Pivot Table from multiple worksheets in the same workbook
{ 328 Comments }
Assume a workbook with two worksheets – Jan and Feb. In both the worksheets, the following exist 1. Same number of columns on both worksheets; and 2. Same order of columns on both worksheets; and 3. Same spellings of headings on both worksheets As you can observe in this workbook, all three aspects mentioned above […]
Read More →
Analysing customer walkin data by date and service taken
{ 5 Comments }
Assume data is in range A3:E10. In A4:A10, various service types are mentioned. In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012. In range B4:E10 are numbers representing number of customers. One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]
Read More →
Compute product wise YTD Revenue from a matrix like/Cross tabular dataset
{ 0 Comments }