A large retailer who specializes in sporting equipment has commissioned us to analyze their sales and revenue. This company sells their sport equipment all over the world and they employ many sales representatives to travel, meet with customers and make sales. Each sales person reports to a sales manager and that sales manager is responsible for the performance of a specific territory.
The C-executive has employed our services and I was commissioned to create this report to analyze the sales performances over time of their multiple territories world wide and provide answers to some key questions they had identified.
Key Features:
Multiple people having various roles will be looking at this report.
Each sales representative must be able to see his own individual data.
Each sales manager should be able to see their own data and all sales reps they managed.
VP of Sales should be able to see all sales people in the report.
In power BI this functionality is accomplished through row level security (RLS) which is a data modeling technique that uses DAX to filter the data that restrict access to data in the model to certain users. This is to ensure people see the information only they need to see.
Key Questions:
VP needs to see summary of each sales persons lifetime sales along with year-over-year sales growth.
Year to date sales totals for current year and compare those to previous years.
This requires using Time Intelligence functions in DAX which allows to calculate sales amounts over various time periods. In addition he needs the ability to drill down to see more detail about each sales person along with their photo and emails.
Creating the Model: After importing the raw data set I made sure it was devoid of duplicate values and missing data. The model was created in STAR Schema style where we had the central Fact table that contains numerical or quantitative data that can be aggregated and has foreign keys referencing the primary keys of related dimension tables surrounded by multiple Dimension tables around it holding descriptive or qualitative attributes that provide context to the data.
Using Time Intelligence Functions in DAX requires us to use at least one date table and also the created table needs to be mark to be used as date table to be able to explicitly tell the data model that table is to be used for time intelligence .
Creating Measures in DAX: Measures are calculations used in Power BI that offer many benefits like reusability, consistency, interactivity, performance optimization and much more. Measure is evaluated in the context of the cell and are dynamic values that change based on what is being viewed in a report and do not increase the model size, is stored in the model as source code and computed only when used in a report.
Few Measures used in this report are like:
Total Sales = Total Sales = SUM(FactResellerSales[SalesAmount])
Total Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(OrderDAte[OrderrDate]))
Total Sales PR YTD = TOTALYTD([Total Sales PY], OrderDate[OrderDate])
Total Sales YoY% = VAR_Prev_YEAR = CALCULATE([Total Sales], DATEADD(OrderDate[OrderDate], -1, YEAR)) RETURN
DIVIDE([Total Sales] - PREV_YEAR, PREVYEAR, 0)
Total Sales YTD = TOTALYTD([Total Sales], OrderDate[OrderDate])
Total Lifetime Sales = CALCULATE([Total Sales], (OrderDate[OrderDate]))
Using the measures above I was able to create a report that satisfied all the requirements asked by the VP Sales that had the ability to see the over all sales picture with the Sales country, Lifetime sales , Total sales, Sales pervious year, and YoY percentage change for each sales person.
VP Sales was given the drill through ability by selecting a sales person it would take to the drill through page where the full information of the sales person was visible along with the performance charts answering question about YoY sales, sales summary , top-selling products, top-selling regions, cumulative monthly sales with comparison and ability to go back to the main report.
Decomposition Tree (AL Visual): Allows you to split a measures value by various dimensions and helps to explain which dimensions contribute the most to the measures value. This helps in root cause analysis of the data across multiple dimensions allowing to trace the higher values and also lower values where improvement is needed or to identify what is driving the higher values and the lower values allowing data exploration at a glance.
I have tried to create a story of my project work to showcase the capabilities of Power BI as well as my abilities to create a interactive and intuitive report that satisfies the ask of stakeholders and will empower the business to manage the future sales growth.
Thank you for reading this article your feedback and comments are appreciated.
Comments