Optimizing DAX queries in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a calculated measure that sums the Sales column.

Power BI

Total Sales = SUM([1])

Drag options to blanks, or click blank then click option'

ASales[Amount]

BSales[Quantity]

CCustomers[Amount]

DProducts[Price]

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table or column name.

Summing a quantity instead of amount.

The SUM function should be applied to the Sales[Amount] column to calculate total sales.

2fill in blank

medium

Complete the code to calculate the average sales per customer.

Power BI

Average Sales per Customer = DIVIDE(SUM(Sales[Amount]), [1])

Drag options to blanks, or click blank then click option'

ACOUNT(Sales[OrderID])

BSUM(Customers[CustomerID])

CCOUNTROWS(Sales)

DDISTINCTCOUNT(Customers[CustomerID])

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT instead of DISTINCTCOUNT.

Counting orders instead of customers.

To get average sales per customer, divide total sales by the distinct count of customers.

3fill in blank

hard

Fix the error in the code to filter sales greater than 1000.

Power BI

High Sales = CALCULATE(SUM(Sales[Amount]), Sales[Amount] [1] 1000)

Drag options to blanks, or click blank then click option'

A<

B<=

C>

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '>'.

Using '<' which filters smaller amounts.

The filter should use '>' to select sales amounts greater than 1000.

4fill in blank

hard

Fill both blanks to create a measure that calculates total sales for the current year only.

Power BI

Sales Current Year = CALCULATE(SUM(Sales[Amount]), [1](Sales, YEAR(Sales[Date]) = [2](TODAY())))

Drag options to blanks, or click blank then click option'

AYEAR

BFILTER

DTODAY

Attempts:

3 left

💡 Hint

Common Mistakes

Using TODAY instead of YEAR(TODAY()).

Not using FILTER to apply the condition.

Use FILTER to filter Sales by YEAR of Sales[Date] equal to current year using YEAR(TODAY()).

5fill in blank

hard

Fill in the blank to create a measure that calculates the percentage of total sales for each product category.

Power BI

Category Sales % = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL([1])), 0) * 100

Drag options to blanks, or click blank then click option'

ACustomers

BProducts

CCategory

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL(Sales) which removes too many filters.

Using ALL(Category) which is not a table.

Use ALL(Products) to remove filters on products and calculate total sales for percentage.