AldoReyes.github.io
View the Project on GitHub Contoso Sales Power BI
Aldo Reyes Portfolio
Contoso Sales Dashboard Power BI
📋 Overview
🧮 Metrics Analysis
📘 Metrics Glossary
📊 Dashboard Design
Contoso Sales Power BI-Dashboard
Overview
This project will create a Power BI dashboard with the source Contoso Sales Sample for Power BI, data Model
The scope is to build an overview page and provide data insights about the current situation of the company (YTD 2013)
The goal is to showcase my work path while building a Power BI solution, focusing solely on critical thinking and decision-making regarding the type of metrics, visualizations, and design elements to make the information easy to read and consume.
Other skills, like data modeling or data analysis, will be shown in other projects.
| Figure 1: Contoso Sales Data Model |
Metrics Analysis
At first glance, one might assume that SalesAmount equals UnitPrice × SalesQuantity.
Check SalesAmount = CALCULATE(SUM(‘Product’[UnitPrice])*SUM(Sales[SalesQuantity]))
However, when testing this logic, the result exceeds the actual SalesAmount, suggesting that discounts are already factored in. This implies that SalesAmount represents net revenue after applying DiscountAmount.
SalesAmount Cal = [Check SalesAmount]-Sum(Sales[DiscountAmount])
📘 Metrics Glossary
This glossary defines the key metrics used in the Contoso Sales analysis. Each entry includes a description, a suggested formula, and a DAX implementation example for Power BI.
🧮 Base Metrics (Explicit Measures)
This section defines the foundational metrics used in the Contoso Sales Dashboard. All metrics are implemented as explicit DAX measures to ensure compatibility with Calculation Groups and maintain clarity across visuals.
| Metric | Description | Suggested Formula | DAX Example |
|---|---|---|---|
| UnitPrice | Unit price of the product. Retrieved from the Product table for consistency. |
(Product[UnitPrice]) |
UnitPrice = SUM('Product'[UnitPrice]) |
| UnitCost | Unit cost of the product. Retrieved from the Product table. |
(Product[UnitCost]) |
UnitCost = SUM(Product[UnitCost]) |
| SalesQuantity | Total quantity of products sold. Defined as an explicit measure. | SUM(Sales[SalesQuantity]) |
SalesQuantity = SUM(Sales[SalesQuantity]) |
| ReturnQuantity | Total quantity of products returned. | SUM(Sales[ReturnQuantity]) |
ReturnQuantity = SUM(Sales[ReturnQuantity]) |
| DiscountAmount | Total discount amount applied to sales. | SUM(Sales[DiscountAmount]) |
DiscountAmount = SUM(Sales[DiscountAmount]) |
| DiscountQuantity | Quantity of products sold with discount applied. | SUM(Sales[DiscountQuantity]) |
DiscountQuantity = SUM(Sales[DiscountQuantity]) |
🧠 Notes
- All metrics above are defined as explicit DAX measures to ensure compatibility with Calculation Groups and advanced Time Intelligence logic.
- Avoid using raw columns directly in visuals or calculations when dynamic logic (e.g., YTD, YoY) is required.
- These measures serve as the foundation for derived metrics such as revenue, cost, and profitability.
💰 Revenue and Cost Metrics
| Metric | Description | Suggested Formula | DAX Example |
|---|---|---|---|
| NetSales | Gross revenue before discounts. | UnitPrice × SalesQuantity |
NetSales = [SalesAmount]+[DiscountAmount] |
| SalesAmount | Net revenue after discounts. | SUM(Sales[SalesAmount]) |
SalesAmount = SUM(Sales[SalesAmount] |
| ReturnAmount | Monetary value of returned products. | SUM(Sales[ReturnAmount] |
ReturnAmount = SUM(Sales[DiscountAmount] |
| TotalCost | Total cost of sold products (excluding returns). | SUM(Sales[TotalCost] |
TotalCost = (SUM(Sales[TotalCost] |
📊 Profitability Metrics
| Metric | Description | Suggested Formula | DAX Example |
|---|---|---|---|
| GrossProfit | Gross profit before returns. | SalesAmount - TotalCost |
GrossProfit = [SalesAmount] - [TotalCost] |
| NetProfit | Net profit after returns. | SalesAmount - TotalCost - ReturnAmount |
NetProfit = [SalesAmount] - [TotalCost] - [ReturnAmount] |
| GrossMargin % | Gross margin as a percentage of sales. | GrossProfit / SalesAmount |
GrossMargin % = DIVIDE([GrossProfit], [SalesAmount]) |
| NetMargin % | Net margin as a percentage of sales. | NetProfit / SalesAmount |
NetMargin % = DIVIDE([NetProfit], [SalesAmount]) |
📉 Discount and Return Metrics
| Metric | Description | Suggested Formula | DAX Example |
|---|---|---|---|
| DiscountRate % | Discount percentage applied over NetSales. | DiscountAmount / NetSales |
DiscountRate % = DIVIDE([DiscountAmount], [NetSales]) |
| ReturnRate % | Return percentage over quantity sold. | ReturnQuantity / SalesQuantity |
ReturnRate % = DIVIDE([ReturnQuantity], [SalesQuantity]) |
⏱️ Time Intelligence Calculation Group
This section defines reusable time-based transformations using Calculation Groups in Power BI. These allow dynamic application of logic (YTD, MTD, YoY, etc.) to any measure using SELECTEDMEASURE().
| Calculation Item | Description | Suggested Formula | DAX Example |
|---|---|---|---|
| YTD | Year-to-date total from Jan 1 to current date. | TOTALYTD(SELECTEDMEASURE(), 'Date'[Date]) |
YTD = TOTALYTD(SELECTEDMEASURE(), 'Calendar'[DateKey]) |
| MTD | Month-to-date total from 1st of month to current date. | TOTALMTD(SELECTEDMEASURE(), 'Date'[Date]) |
MTD = TOTALMTD(SELECTEDMEASURE(), 'Calendar'[DateKey]) |
| QTD | Quarter-to-date total from start of quarter to today. | TOTALQTD(SELECTEDMEASURE(), 'Date'[Date]) |
QTD = TOTALQTD(SELECTEDMEASURE(), 'Calendar'[DateKey]) |
| YoY | Same period last year. | CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date])) |
YoY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[DateKey])) |
| Previous Month | Same period in previous month. | CALCULATE(SELECTEDMEASURE(), PREVIOUSMONTH('Date'[Date])) |
PreviousMonth = CALCULATE(SELECTEDMEASURE(), PREVIOUSMONTH('Calendar'[DateKey])) |
| YoY % Change | Year-over-year percentage change. | (Current - LastYear) / LastYear |
YoY % = DIVIDE(SELECTEDMEASURE() - [YoY], [YoY]) |
| MoM % Change | Month-over-month percentage change. | (Current - PreviousMonth) / PreviousMonth |
MoM % = DIVIDE(SELECTEDMEASURE() - [PreviousMonth], [PreviousMonth]) |
Technical Notes
- Metrics are calculated using data from the
ProductandSalestables.- Ensure consistency between
UnitPriceandUnitCostacross tables.- Formulas are adaptable to DAX, SQL, or other BI environments.
Dashboard Design
| Select Key Visualizations |
|---|
| KIPs that could provide us with a simple Overview of the company’s Sales statement. |
| Yearly Table to have a better perspective of the metrics comparing through time. |
| Histogram for 2013 vs LY Sales |
| Time Intelligence Measure Group with YTD, |
| YOY displays 2012 vs 2011 Sales |
| YOY% disable the KIP’s and table but privides a comparative line for 2012 vs 2013 Sales on our histogram visualization |
| Channel, Category and Subcategory Sales Bar Chart interactive filters for the rest of the dashboard. |
| Bubble Map drilled down by Continent/Contry, interactive for the rest of the dashboard. |
| Most profitable products interactive with the rest of the dashboard |
The Story here is, KPI’s are showing a healty preformance for 2013 55.79% NetMargin with low ReturnRate and DiscountAmount
But if we take a deeper look into the Data Table we can notice YOY% SalesAmount decreased -15.96% in 2012 and the negative tendance was contenined in 2013 up to -3.33%
Altought the Margins fell it’s not a percentage to be concider. Same goes for DiscountRate and ReturnRate is also lower.
In conclution, Sales has drop significantly vs LY in 2012 and the tendance has been containde in 2013