This post covers 50 intermediate to advanced Power BI DAX queries with practical use cases. It focuses on real-world scenarios, interview-oriented questions, and commonly used DAX patterns to help you build strong analytical skills.
1. Total Sales
Total Sales = SUM(Sales[SalesAmount])
2. Total Cost
Total Cost = SUM(Sales[CostAmount])
3. Profit
Profit = [Total Sales] - [Total Cost]
4. Profit Margin %
Profit Margin % = DIVIDE([Profit], [Total Sales])
5. Order Count
Order Count = DISTINCTCOUNT(Sales[OrderID])
6. Customer Count
Customer Count = DISTINCTCOUNT(Sales[CustomerID])
7. Average Order Value
AOV = DIVIDE([Total Sales], [Order Count])
8. Sales YTD
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
9. Sales MTD
Sales MTD = TOTALMTD([Total Sales], 'Date'[Date])
10. Sales QTD
Sales QTD = TOTALQTD([Total Sales], 'Date'[Date])
11. Sales Last Year
Sales LY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
12. YoY Growth %
YoY Growth % =
DIVIDE([Total Sales] - [Sales LY], [Sales LY])
13. Sales Previous Month
Sales PM =
CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))
14. Rolling 3 Month Sales
Rolling 3M Sales =
CALCULATE(
[Total Sales],
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
)
15. Rolling 12 Month Sales
Rolling 12M Sales =
CALCULATE(
[Total Sales],
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)
16. Sales All Products
Sales All Products =
CALCULATE([Total Sales], ALL(Products))
17. Sales Contribution %
Sales Contribution % =
DIVIDE([Total Sales], [Sales All Products])
18. Sales Ignore Date
Sales Ignore Date =
CALCULATE([Total Sales], REMOVEFILTERS('Date'))
19. Sales by Country Only
Sales by Country =
CALCULATE(
[Total Sales],
ALLEXCEPT(Customers, Customers[Country])
)
20. Keep Product Filter
Tech Sales =
CALCULATE(
[Total Sales],
KEEPFILTERS(Products[Category] = "Technology")
)
21. Product Rank by Sales
Product Rank =
RANKX(
ALL(Products[ProductName]),
[Total Sales],
,
DESC
)
22. Top 10 Products
Top 10 Product Sales =
CALCULATE(
[Total Sales],
TOPN(10, Products, [Total Sales], DESC)
)
23. Bottom 5 Products
Bottom 5 Product Sales =
CALCULATE(
[Total Sales],
TOPN(5, Products, [Total Sales], ASC)
)
24. Running Total
Running Total =
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
25. Cumulative Sales by Product
Cumulative Product Sales =
CALCULATE(
[Total Sales],
FILTER(
ALL(Products),
Products[ProductName] <= MAX(Products[ProductName])
)
)
26. Pareto %
Pareto % =
VAR TotalSalesAll =
CALCULATE([Total Sales], ALL(Products))
VAR ProductSales =
ADDCOLUMNS(
ALL(Products),
"Sales", [Total Sales]
)
VAR CumSales =
SUMX(
FILTER(
ProductSales,
[Sales] >= EARLIER([Sales])
),
[Sales]
)
RETURN
DIVIDE(CumSales, TotalSalesAll)
27. Sales Target Variance
Sales Variance = [Total Sales] - [Sales Target]
28. Sales Target Achievement %
Sales Achievement % =
DIVIDE([Total Sales], [Sales Target])
29. Dynamic KPI Status
Sales Status =
IF([Total Sales] >= [Sales Target], "Achieved", "Not Achieved")
30. Average Sales per Customer
Avg Sales per Customer =
DIVIDE([Total Sales], [Customer Count])
31. High Value Customers
High Value Sales =
CALCULATE(
[Total Sales],
FILTER(Customers, [Total Sales] > 50000)
)
32. Sales for Selected Year
Sales Selected Year =
CALCULATE(
[Total Sales],
ALL('Date'),
VALUES('Date'[Year])
)
33. Max Sales Day
Max Sales Day =
MAXX(VALUES('Date'[Date]), [Total Sales])
34. Min Sales Day
Min Sales Day =
MINX(VALUES('Date'[Date]), [Total Sales])
35. Average Monthly Sales
Avg Monthly Sales =
AVERAGEX(
VALUES('Date'[Month]),
[Total Sales]
)
36. Sales with No Discount
Sales No Discount =
CALCULATE([Total Sales], Sales[Discount] = 0)
37. Discounted Sales
Discounted Sales =
CALCULATE([Total Sales], Sales[Discount] > 0)
38. Discount Impact %
Discount Impact % =
DIVIDE([Discounted Sales], [Total Sales])
39. Customer First Purchase Date
First Purchase Date =
MIN(Sales[OrderDate])
40. Customer Last Purchase Date
Last Purchase Date =
MAX(Sales[OrderDate])
41. Inactive Customers
Inactive Customers =
CALCULATE(
DISTINCTCOUNT(Customers[CustomerID]),
FILTER(
Customers,
ISBLANK([Total Sales])
)
)
42. Sales Using USERELATIONSHIP
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])
)
43. Dynamic Measure by Selection
Dynamic Sales =
SWITCH(
SELECTEDVALUE(Metrics[Metric]),
"Sales", [Total Sales],
"Profit", [Profit],
"Quantity", SUM(Sales[Quantity])
)
44. Sales Using TREATAS
Sales by Region =
CALCULATE(
[Total Sales],
TREATAS(VALUES(Region[Region]), Customers[Region])
)
45. Sales with Cross Filter
Sales BI Direction =
CALCULATE(
[Total Sales],
CROSSFILTER(Sales[CustomerID], Customers[CustomerID], BOTH)
)
46. Last Non Blank Sales
Last Sales Value =
LASTNONBLANK('Date'[Date], [Total Sales])
47. % Difference from Average
Diff from Avg % =
DIVIDE([Total Sales] - AVERAGE([Total Sales]), AVERAGE([Total Sales]))
48. Sales Growth Index
Sales Index =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL('Date')))
49. Sales Per Day
Sales Per Day =
DIVIDE([Total Sales], DISTINCTCOUNT('Date'[Date]))
50. Conditional Color Logic
Sales Color =
IF([Total Sales] >= [Sales Target], "Green", "Red")
