Variables (VAR/RETURN) in Power BI - Dashboard Guide

Dashboard Mode - Variables (VAR/RETURN)

Business Question

How can we use variables in DAX to calculate the average sales per order and highlight orders above this average?

Sample Data

OrderID	Customer	SalesAmount	OrderDate
1001	Alice	250	2024-01-10
1002	Bob	400	2024-01-15
1003	Charlie	150	2024-01-20
1004	Diana	500	2024-01-25
1005	Eva	300	2024-01-30

Dashboard Components

• KPI Card: Average Sales per Order
  Formula:
  

  Average Sales =
  VAR TotalSales = SUM(Sales[SalesAmount])
  VAR OrderCount = COUNTROWS(Sales)
  RETURN DIVIDE(TotalSales, OrderCount)

  
  Result: (250+400+150+500+300)/5 = 320
• Table: Orders with Highlight
  Columns: OrderID, Customer, SalesAmount, Above Average?
  Formula for "Above Average?":
  

  Above Average? =
  VAR AvgSales = CALCULATE(
      DIVIDE(SUM(Sales[SalesAmount]), COUNTROWS(Sales))
  )
  RETURN IF(Sales[SalesAmount] > AvgSales, "Yes", "No")

  
  Rows:
  1001 - 250 - No
  1002 - 400 - Yes
  1003 - 150 - No
  1004 - 500 - Yes
  1005 - 300 - No
• Bar Chart: Sales Amount by Customer
  X-axis: Customer
  Y-axis: SalesAmount
  Bars show sales per customer

Dashboard Layout

+----------------------+----------------------+
|  Average Sales KPI   |   Sales by Customer  |
|       (320)          |      (Bar Chart)     |
+----------------------+----------------------+
|               Orders with Highlight Table           |
|                (OrderID, Customer, SalesAmount, Above Average?)              |
+-----------------------------------------------------+

Interactivity

A slicer on OrderDate allows filtering orders by date range. When the date range changes, the Average Sales KPI, Orders with Highlight Table, and Sales by Customer Bar Chart update automatically to reflect only the selected orders.

Self Check

If you add a filter to show only orders with SalesAmount greater than 300, which components update and how?

• The Average Sales KPI recalculates the average using only orders with sales above 300 (orders 1002 and 1004), so average becomes (400+500)/2 = 450.
• The Orders with Highlight Table shows only orders 1002 and 1004, with 1002 marked "No" (400 < 450) and 1004 marked "Yes" (500 > 450) for "Above Average?".
• The Sales by Customer Bar Chart updates to show only customers Bob and Diana with their sales amounts.

Key Result

Dashboard showing average sales per order using variables, highlighting orders above average, with sales by customer chart.