1. How do you calculate Running Total using DAX?
Answer:
Running Total =
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
ALLSELECTED(Sales[Date]),
Sales[Date] <= MAX(Sales[Date])
)
)
2. How do you calculate Year-over-Year (YoY) Growth?
Answer:
YoY Growth =
DIVIDE(
[Total Sales] - CALCULATE([Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])),
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
)
3. How do you calculate Sales of Previous Month?
Answer:
Previous Month Sales =
CALCULATE(
[Total Sales],
PREVIOUSMONTH('Date'[Date])
)
4. How do you get the Second Highest Sales using DAX?
Answer:
Second Highest Sales =
CALCULATE(
MAX(Sales[SalesAmount]),
FILTER(
Sales,
Sales[SalesAmount] < CALCULATE(MAX(Sales[SalesAmount]))
)
)
5. What is the use of CALCULATE in DAX?
Answer:
CALCULATE modifies the context of a calculation. It's used to apply filters
and change evaluation context.
6. How to calculate Cumulative Sales with dynamic slicers (e.g., Region,
Segment)?
Answer:
Cumulative Sales =
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
)
)
7. How to calculate Percentage of Total?
Answer:
% of Total Sales =
DIVIDE(
[Total Sales],
CALCULATE([Total Sales], ALL(Sales))
)
8. How to find Top N Products by Sales?
Answer:
Create a measure:
Top N Sales = [Total Sales]
Then apply Top N filter in visual-level filters or use RANKX for more
control:
Rank by Sales =
RANKX(
ALL(Product[ProductName]),
[Total Sales],
,
DESC
)
9. How to calculate Sales Difference from Previous Year?
Answer:
Sales Diff YoY =
[Total Sales] - CALCULATE([Total Sales],
SAMEPERIODLASTYEAR('Date'[Date]))
10. How to use USERELATIONSHIP to work with inactive relationships?
Answer:
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])
)
11. How to calculate Profit Margin %?
Answer:
Profit Margin % =
DIVIDE([Total Profit], [Total Sales]) * 100
12. How to create a Dynamic Measure based on slicer selection?
Answer:
Using a disconnected table (e.g., MeasureSelector) and SWITCH:
Selected Measure =
SWITCH(
SELECTEDVALUE(MeasureSelector[Measure]),
"Sales", [Total Sales],
"Profit", [Total Profit],
"Quantity", [Total Quantity]
)
13. How do you calculate average sales per customer?
Answer:
Avg Sales per Customer =
DIVIDE([Total Sales], DISTINCTCOUNT(Sales[CustomerID]))
14. How to calculate Rolling 3-Month Average Sales?
Answer:
Rolling 3 Month Avg Sales =
AVERAGEX(
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
[Total Sales]
)
15. How to create a KPI indicator using DAX?
Answer:
KPI Status =
SWITCH(
TRUE(),
[Sales Growth %] > 10, "Good",
[Sales Growth %] > 0, "Average",
"Poor"
)
16. How to calculate Last Non-Blank value (e.g., latest stock price)?
Answer:
Latest Sales =
CALCULATE(
LASTNONBLANKVALUE('Date'[Date], [Total Sales]),
[Total Sales]
)
17. How to compare sales of current month vs. same month last year?
Answer:
Sales Same Month LY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
)
18. How to rank customers based on total sales?
Answer:
Customer Rank =
RANKX(
ALL(Customer[CustomerName]),
[Total Sales],
,
DESC
)
19. How to create a Dynamic Title in a Card Visual using DAX?
Answer:
Dynamic Title =
"Sales Report for " & SELECTEDVALUE('Region'[RegionName])
20. How to filter a table using a DAX measure (virtual table)?
Answer:
Top Customers Table =
TOPN(
5,
SUMMARIZE(Sales, Customer[CustomerName], "Sales", [Total Sales]),
[Total Sales],
DESC
)
No comments:
Post a Comment