Day 18: Power BI — Sales Analytics Use Cases

Essential measures: Total Revenue = SUM(Sales[Revenue]). Sales Target = SUM(Targets[Target]). Achievement % = DIVIDE([Total Revenue], [Sales Target]). Revenue vs Last Year = [Total Revenue] - [Revenue Last Year]. Key visuals: Card KPIs for Revenue, Target, Achievement %, and YoY growth. Column chart for Revenue vs Target by Region. Line chart for Monthly Revenue Trend with last year comparison. Bar chart for Top 10 Sales Reps. Matrix for Revenue by Region and Product Category. Slicers for Year, Quarter, Region.

Method 1: Clustered column chart with two series — Revenue and Target side by side. Method 2: Column chart for Revenue with a constant line for Target (Analytics pane → Constant Line → enter target value or use a measure). Method 3: Bullet chart (custom visual from AppSource) — shows actual as a bar, target as a marker, and performance zones as background. Method 4: KPI visual (built-in) — shows current value, trend, and target status with automatic green/red colouring. The KPI visual is fastest to set up for a single metric.

Two approaches: (1) Visual-level Top N filter: select the visual → Filters pane → drag the dimension field to "Filters on this visual" → filter type = Top N → N = 10 → "By value" = your revenue measure. Shows only the top 10 items in that visual. (2) TOPN in DAX: creates a table of top N items that can be used in CALCULATE: Top 10 Revenue = CALCULATE([Total Revenue], TOPN(10, Products, [Total Revenue], DESC)). The visual filter approach is simpler; the DAX approach gives more control and can be used in measures.

New vs Returning Customer Flag: create a measure that compares a customer's first purchase date to the current filter context date. New Customers = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(ALL(Sales), Sales[OrderDate] = MIN(Sales[OrderDate]))). Repeat Customers = [Total Customers] - [New Customers]. Repeat Purchase Rate = DIVIDE([Repeat Customers], [Total Customers]). Visual: line chart showing New vs Repeat customer count by month. Cohort analysis tracks a group of customers acquired in a specific month and how many are still purchasing in subsequent months.

A profitability matrix plots products on two axes — Revenue (X axis) and Margin % (Y axis) — using a scatter chart. Each bubble is a product, bubble size represents quantity sold. This immediately shows: high revenue, high margin (stars — prioritise), high revenue, low margin (volume drivers — watch costs), low revenue, high margin (niche — grow), low revenue, low margin (review — potential discontinuation). Measures needed: Total Revenue, Gross Margin % = DIVIDE([Revenue] - [Cost], [Revenue]), Total Quantity. Set bubble size = Total Quantity.