Power BI DAX examples
This is the smaller reporting section of FormulaExamples.com. It supports the spreadsheet formula library with DAX measures for KPI dashboards and reporting.
DAX Functions
CALCULATE function
Total Sales Filtered = CALCULATE([Total Sales], Sales[Region] = "East")
📉SUMX function
Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
📉AVERAGEX function
Avg Order Value = AVERAGEX(VALUES(Sales[Order ID]), [Total Sales])
📉COUNTROWS function
Order Count = COUNTROWS(Sales)
📉DISTINCTCOUNT function
Customer Count = DISTINCTCOUNT(Sales[Customer ID])
📉DIVIDE function
Margin % = DIVIDE([Gross Margin], [Revenue], 0)
📉FILTER function
Large Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Amount] > 1000))
📉ALL function
Sales All Regions = CALCULATE([Total Sales], ALL(Sales[Region]))
📉ALLSELECTED function
Sales Selected = CALCULATE([Total Sales], ALLSELECTED(Sales))
📉ALLEXCEPT function
Sales by Customer = CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[Customer ID]))
📉VALUES function
Product Count = COUNTROWS(VALUES(Product[Product Name]))
📉SELECTEDVALUE function
Selected Region = SELECTEDVALUE(Sales[Region], "All Regions")
📉RELATED function
Product Category = RELATED(Product[Category])
📉RELATEDTABLE function
Orders per Customer = COUNTROWS(RELATEDTABLE(Sales))
📉SWITCH function in DAX
Status = SWITCH(TRUE(), [Margin %] > .4, "High", [Margin %] > .2, "Medium", "Low")
📉IF function in DAX
Target Status = IF([Total Sales] >= [Target], "Met", "Missed")
📉VAR and RETURN in DAX
Margin % = VAR Revenue = [Total Sales] RETURN DIVIDE([Gross Margin], Revenue, 0)
KPI Measures
Total Sales measure
Total Sales = SUM(Sales[Amount])
📉Total Cost measure
Total Cost = SUM(Sales[Cost])
📉Gross Margin measure
Gross Margin = [Total Sales] - [Total Cost]
📉Gross Margin Percent measure
Gross Margin % = DIVIDE([Gross Margin], [Total Sales], 0)
📉Average Order Value measure
Average Order Value = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[Order ID]), 0)
📉Customer Count measure
Customer Count = DISTINCTCOUNT(Sales[Customer ID])
📉Orders measure
Orders = DISTINCTCOUNT(Sales[Order ID])
📉Units Sold measure
Units Sold = SUM(Sales[Quantity])
📉Conversion Rate measure
Conversion Rate = DIVIDE([Conversions], [Visitors], 0)
📉Churn Rate measure
Churn Rate = DIVIDE([Customers Lost], [Customers at Start], 0)
📉Retention Rate measure
Retention Rate = 1 - [Churn Rate]
📉CAC measure
CAC = DIVIDE([Sales and Marketing Cost], [New Customers], 0)
📉ROAS measure
ROAS = DIVIDE([Ad Revenue], [Ad Spend], 0)
📉LTV measure
LTV = DIVIDE([ARPU] * [Gross Margin %], [Churn Rate], 0)
📉Revenue per Employee measure
Revenue per Employee = DIVIDE([Revenue], [Employee Count], 0)
📉Inventory Turnover measure
Inventory Turnover = DIVIDE([COGS], [Average Inventory], 0)
Time Intelligence
YTD Sales measure
YTD Sales = TOTALYTD([Total Sales], Date[Date])
📉MTD Sales measure
MTD Sales = TOTALMTD([Total Sales], Date[Date])
📉QTD Sales measure
QTD Sales = TOTALQTD([Total Sales], Date[Date])
📉Previous Month Sales measure
Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(Date[Date]))
📉Previous Year Sales measure
Previous Year Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
📉Year over Year Growth measure
YoY Growth % = DIVIDE([Total Sales] - [Previous Year Sales], [Previous Year Sales], 0)
📉Month over Month Growth measure
MoM Growth % = DIVIDE([Total Sales] - [Previous Month Sales], [Previous Month Sales], 0)
📉Rolling 3 Month Sales measure
Rolling 3M Sales = CALCULATE([Total Sales], DATESINPERIOD(Date[Date], MAX(Date[Date]), -3, MONTH))
📉Rolling 12 Month Sales measure
Rolling 12M Sales = CALCULATE([Total Sales], DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH))
📉Running Total measure
Running Total = CALCULATE([Total Sales], FILTER(ALL(Date[Date]), Date[Date] <= MAX(Date[Date])))
📉DATEADD function
Sales Previous Period = CALCULATE([Total Sales], DATEADD(Date[Date], -1, MONTH))
📉DATESYTD function
Sales YTD = CALCULATE([Total Sales], DATESYTD(Date[Date]))
📉SAMEPERIODLASTYEAR function
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
📉PARALLELPERIOD function
Sales Previous Quarter = CALCULATE([Total Sales], PARALLELPERIOD(Date[Date], -1, QUARTER))
📉DATESINPERIOD function
Sales Last 90 Days = CALCULATE([Total Sales], DATESINPERIOD(Date[Date], MAX(Date[Date]), -90, DAY))
Reporting
DAX ranking measure
Product Rank = RANKX(ALL(Product[Product Name]), [Total Sales], , DESC)
📉DAX percent of total measure
Sales % of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)), 0)
📉DAX dynamic title measure
Report Title = "Sales for " & SELECTEDVALUE(Date[Year], "All Years")
📉DAX conditional formatting measure
Color = IF([Margin %] >= .3, "Green", "Red")
📉DAX target status measure
Target Status = IF([Total Sales] >= [Sales Target], "On Track", "Behind")
📉DAX top N measure
Top N Sales = IF([Product Rank] <= 10, [Total Sales])
📉DAX customer segmentation measure
Segment = SWITCH(TRUE(), [Total Sales] > 10000, "High", [Total Sales] > 1000, "Medium", "Low")
📉DAX active customers measure
Active Customers = CALCULATE(DISTINCTCOUNT(Sales[Customer ID]), Sales[Status] = "Active")
📉DAX new customers measure
New Customers = CALCULATE(DISTINCTCOUNT(Sales[Customer ID]), Sales[Customer Type] = "New")
📉DAX repeat customers measure
Repeat Customers = CALCULATE(DISTINCTCOUNT(Sales[Customer ID]), Sales[Customer Type] = "Repeat")