DAX and Visualization Assessment
This assessment evaluates your ability to create DAX calculations, interpret filter context, select suitable visualizations, and build meaningful reports in Power BI. Complete the practical tasks and knowledge checks using a valid sales dataset.
Assessment Scenario
The reporting team at CIIT TRANING INSTITUTE needs a sales report that shows revenue, profit, order counts, monthly trends, and product performance. Your task is to create the required measures and visualizations and verify that the report responds correctly to user selections.
1. Assessment Objectives
- Write DAX measures for business calculations.
- Use aggregation and iterator functions appropriately.
- Understand how filter context affects measure results.
- Choose visualizations based on the question being answered.
- Build an interactive report using slicers and visual interactions.
- Validate calculations against source data.
- Present findings clearly and accurately.
Assessment Workflow
2. Dataset Requirements
Use a sales table named Sales with columns similar to the following. If your actual model uses different names, update the DAX expressions accordingly.
| Column | Example | Data type |
|---|---|---|
| OrderID | ORD1001 | Text |
| OrderDate | 2026-01-15 | Date |
| Product | Laptop | Text |
| Region | West | Text |
| Quantity | 3 | Whole Number |
| UnitPrice | 25000 | Decimal Number |
| UnitCost | 18000 | Decimal Number |
3. DAX Assessment: Aggregation Measures
Task 1: Calculate Total Quantity
This measure adds the quantities from the rows included in the current filter context.
Task 2: Calculate Total Sales
SUMX evaluates the multiplication for each row and then adds the resulting values. This is useful when revenue is calculated from quantity multiplied by unit price.
Task 3: Calculate Total Cost
Task 4: Calculate Total Profit
Task 5: Calculate Profit Margin
Format Profit Margin % as a percentage. DIVIDE handles a zero denominator using the alternate result specified in the third argument.
How the Measures Relate
4. DAX Assessment: Filter Context
A measure is recalculated based on the filters applied to the report. For example, a Total Sales card can show all sales when no region is selected and only West region sales when the West region is selected.
Task 6: Calculate Sales for the West Region
CALCULATE evaluates a measure in a modified filter context. This expression applies a filter for the West region.
Task 7: Calculate Distinct Orders
DISTINCTCOUNT counts unique order identifiers in the current filter context. This may differ from the number of rows when one order has multiple transaction lines.
5. Visualization Assessment
Choose visuals that match the question. Avoid selecting a chart only because it looks attractive; consider what the user needs to compare, track, or investigate.
| Business question | Recommended visual | Field or measure |
|---|---|---|
| What is the total revenue? | Card | Total Sales |
| How does revenue change over time? | Line chart | OrderDate, Total Sales |
| Which products generate the most revenue? | Bar chart | Product, Total Sales |
| How does each region perform? | Column or bar chart | Region, Total Sales or Total Profit |
| What are the detailed order values? | Table | OrderID, Product, Quantity, Total Sales |
| How can users explore a specific period? | Slicer | OrderDate or Year |
Visual Selection Guide
6. Build the Assessment Report
- Create cards for Total Sales, Total Profit, Total Orders, and Profit Margin %.
- Add a line chart showing Total Sales by month.
- Add a bar chart showing Total Sales by Product.
- Add a regional comparison chart using Total Profit or Total Sales.
- Add a table with order-level details.
- Add Year, Region, and Product slicers.
- Use consistent titles, number formats, spacing, and colors.
- Test the report with different filter selections.
Suggested Report Layout
The displayed numbers are examples for layout planning only. Use values calculated from your own dataset in the actual report.
7. Practical Assessment Tasks
Part A: DAX Calculations
- Create Total Quantity.
- Create Total Sales using SUMX.
- Create Total Cost using SUMX.
- Create Total Profit.
- Create Profit Margin % using DIVIDE.
- Create Total Orders using DISTINCTCOUNT.
- Create a measure for sales in a specified region using CALCULATE.
Part B: Visualizations
- Create four KPI cards.
- Create a monthly sales line chart.
- Create a product comparison bar chart.
- Create a regional performance chart.
- Create an order details table.
- Add Year, Region, and Product slicers.
Part C: Validation
- Compare Total Sales with a trusted source total.
- Check that Total Profit equals Total Sales minus Total Cost.
- Test each slicer individually and in combination.
- Write three findings supported by the report results.
8. Assessment Questions
Question 1: What is the difference between SUM and SUMX?
SUM adds the values in a single column. SUMX evaluates an expression for each row of a table and then sums the resulting values.
Question 2: Why is DIVIDE useful in a measure?
DIVIDE performs division and allows an alternate result to be returned when the denominator is zero or blank.
Question 3: What does CALCULATE do?
CALCULATE evaluates an expression in a modified filter context.
Question 4: Which visual is suitable for monthly sales trends?
A line chart is generally suitable for showing how sales change across dates or months.
Question 5: Why can Total Orders differ from Transaction Rows?
A single order may contain multiple transaction rows. DISTINCTCOUNT counts unique order identifiers, while COUNTROWS counts records.
Question 6: What should you do if a slicer changes a total unexpectedly?
Check the filter context, relationships, source data, and measure logic. Compare the filtered result with an independent calculation.
9. Evaluation Rubric
| Assessment area | Points | Expected result |
|---|---|---|
| DAX measures | 30 | Measures calculate correctly and use suitable functions. |
| Visualization choices | 20 | Charts answer the intended business questions. |
| Report layout and formatting | 15 | Report is clear, consistent, and readable. |
| Interactivity | 15 | Slicers and visual interactions behave correctly. |
| Validation and accuracy | 15 | Calculations are checked against trusted results. |
| Documentation | 5 | Measures and findings are documented clearly. |
| Total | 100 | Complete and validated submission |
10. Submission Checklist
- Power BI report file (.pbix).
- Screenshot of the final report page.
- List of DAX measures created.
- Brief explanation of visual selection.
- Validation results for the main measures.
- Three findings supported by the report.
Summary
In this assessment, you practiced writing DAX measures, applying filter context, selecting appropriate visuals, creating interactive reports, and validating calculations.
- Use SUM for straightforward column aggregation.
- Use SUMX when an expression must be evaluated row by row.
- Use CALCULATE to evaluate an expression under modified filters.
- Use DIVIDE for safer division.
- Choose visuals that match the question being answered.
- Test report interactions and compare results with trusted data.
A strong Power BI report combines accurate calculations, effective visual design, meaningful interactivity, and clear communication.