DAX Workout 025 - Mastering Basic DAX Calculations in Power BI

Workouts DAX
1

DAX is a powerful formula language used in Power BI. Dive into its core functionalities and understand basic calculations in this quick workout.

Scenario:

You’re working with a Power BI report on sales data. The dataset includes columns like Product, Quantity Sold, Price, and Region. Use DAX formulas to derive insights.

Objectives:

By the end of this workout, you should be able to:

  1. Understand the basics of DAX formula syntax.

  2. Perform simple calculations using DAX.

Interactive Task:

Given the following tasks, write or identify the correct DAX formula:

  1. Calculate the total sales.

Your Answer: ________________________

  1. Find the average sales price for all products.

Your Answer: ________________________

Given the following DAX formula, identify its purpose:


COUNTROWS(ALL('Sales'))

Expected output:

  • i) Counts all rows in the Sales table without any filters.

  • ii) Counts all rows in the Sales table with current filters.

  • iii) Returns all rows from the Sales table.

  • iv) Calculates the sum of all sales.

Questions:

Which DAX function can you use to calculate the sum of a column in Power BI?

  • i) SUMMARIZE

  • ii) TOTAL

  • iii) AGGREGATE

  • iv) SUM

If you want to calculate the average of a column Price, which of the following DAX formulas is correct?

  • i) AVERAGE(‘Table’[Price])

  • ii) AVG(Table[Price])

  • iii) CALCULATE(MEAN(‘Table’[Price]))

  • iv) MEAN(‘Table’[Price])

Duration: 20 minutes

Difficulty: Beginner

Period
This workout will be released on Tuesday, September 5, 2023, and will end on Thursday, September 28, 2023. But you can always come back to any of the workouts and solve them.

4

Thank you for your workout.
Please find my answers as below:
Interactive Task:

Given the following tasks, write or identify the correct DAX formula:

  1. Calculate the total sales.

Your Answer: Total Sales = SUM(Sales[SalesAmount])

  1. Find the average sales price for all products.

Your Answer: Average Sales Price = AVERAGE(Sales[SalesAmount])

Given the following DAX formula, identify its purpose:

COUNTROWS(ALL(‘Sales’))

  • counting the total number of rows in the ‘Sales’ table after removing any filters or context applied to the table.

Expected output:

  • i) Counts all rows in the Sales table without any filters.

Questions:

Which DAX function can you use to calculate the sum of a column in Power BI?

  • iv) SUM

If you want to calculate the average of a column Price, which of the following DAX formulas is correct?

  • i) AVERAGE(‘Table’[Price])
1 Like
5

Hi - Thanks for the workout. Please find my responses below.

interactive Task:

Given the following tasks, write or identify the correct DAX formula:

  1. Calculate the total sales.

Your Answer: ____Total Sales = Sumx(Sales, Quantity Sold * Price)

  1. Find the average sales price for all products.

Your Answer: AVERAGEX(Sales, Total Sales)

Given the following DAX formula, identify its purpose:

COUNTROWS(ALL('Sales')) 

3 Counts all rows in the Sales table without any filters context both internal or external.

Expected output:

* i) Counts all rows in the Sales table without any filters.
* ii) Counts all rows in the Sales table with current filters.
* iii) Returns all rows from the Sales table.
* iv) Calculates the sum of all sales.

**Questions:**

Which DAX function can you use to calculate the sum of a column in Power BI?

* i) SUMMARIZE
* ii) TOTAL
* iii) AGGREGATE
* iv) SUM

SUM

If you want to calculate the average of a column Price, which of the following DAX formulas is correct?

* i) AVERAGE(‘Table’[Price])
* ii) AVG(Table[Price])
* iii) CALCULATE(MEAN(‘Table’[Price]))
* iv) MEAN(‘Table’[Price])

AVERAGE(‘Table’[Price])

Thanks
Ankit J
6

Hi @EnterpriseDNA,

Here is my solution to this workout:

Inactive Task:
Given the following tasks, write or identify the correct DAX formula:

  1. Calculate the total sales.

Answer:
Sure, to calculate the total sales, you can use the following DAX formula:

Total Sales = SUMX('Table', 'Table'[Quantity Sold] * 'Table'[Price])
  1. Find the average sales price for all products.

Your Answer:
Average Sales Price = AVERAGEX(‘Table’, ‘Table’[Price])

3)Given the following DAX formula, identify its purpose:

COUNTROWS(ALL('Sales'))

Expected output:

  • i) Counts all rows in the Sales table without any filters.

Questions:
Which DAX function can you use to calculate the sum of a column in Power BI?

Answer:

  • iv) SUM

If you want to calculate the average of a column Price, which of the following DAX formulas is correct?
Answer:

  • i) AVERAGE(‘Table’[Price])

Thanks for the workout.
Keith