Basic DAX Functions and Creating Calculations - AnalysisWithSadiul

In Power BI, you can use DAX (Data Analysis Expressions) functions to perform data analysis and calculations. Here’s a detailed guide on basic DAX functions and how to create calculations:

Basic DAX Functions and Creating Calculations

 

 1. Basic DAX Functions

 

1. SUM():

   - Usage: Used to calculate the total sum of a column.

   - Syntax: `SUM(Table[Column])`

   - Example: `SUM(Sales[Amount])` - Calculates the total of the Amount column from the Sales table.

2. AVERAGE():

   - Usage: Used to calculate the average value of a column.

   - Syntax: `AVERAGE(Table[Column])`

   - Example: `AVERAGE(Sales[Amount])` - Finds the average of the Amount column from the Sales table.

3. COUNT():

   - Usage: Used to count the number of non-null rows in a column.

   - Syntax: `COUNT(Table[Column])`

   - Example: `COUNT(Sales[TransactionID])` - Counts the number of rows in the TransactionID column.

4. COUNTA():

   - Usage: Counts the number of non-empty values (text, numbers, or others) in a column.

   - Syntax: `COUNTA(Table[Column])`

   - Example: `COUNTA(Sales[CustomerName])` - Counts how many names are in the CustomerName column.

5. MAX():

   - Usage: Used to find the maximum value in a column.

   - Syntax: `MAX(Table[Column])`

   - Example: `MAX(Sales[Amount])` - Finds the maximum value in the Amount column.

6. MIN():

   - Usage: Used to find the minimum value in a column.

   - Syntax: `MIN(Table[Column])`

   - Example: `MIN(Sales[Amount])` - Finds the minimum value in the Amount column.

7. IF():

   - Usage: Checks a condition and returns one value if the condition is true and another if it’s false.

   - Syntax: `IF(condition, value_if_true, value_if_false)`

   - Example: `IF(Sales[Amount] > 1000, "High", "Low")` - Returns "High" if the Amount is greater than 1000, otherwise "Low".

8. CALCULATE():

   - Usage: Evaluates an expression in a modified context.

   - Syntax: `CALCULATE(expression, filter1, filter2, ...)`

   - Example: `CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North")` - Calculates the total Amount for the North region.

9. FILTER():

   - Usage: Filters rows in a table based on a condition.

   - Syntax: `FILTER(Table, condition)`

   - Example: `FILTER(Sales, Sales[Amount] > 500)` - Filters rows in the Sales table where the Amount is greater than 500.

 

 2. Creating Calculations

 

1. New Column:

   - Usage: Creates a new column where a calculation is performed for each row.

   - Steps:

     1. In Power BI Desktop, go to the "Modeling" tab and select "New Column."

     2. Write a DAX expression, e.g.: `Profit = Sales[Amount] - Sales[Cost]`

2. New Measure:

   - Usage: Creates a new measure that can be used in reports and is calculated at the report level.

   - Steps:

     1. In the "Modeling" tab, select "New Measure."

     2. Write a DAX expression, e.g.: `Total Sales = SUM(Sales[Amount])`

3. Calculated Table:

   - Usage: Creates a new table calculated by a DAX expression.

   - Steps:

     1. Go to the "Modeling" tab and select "New Table."

     2. Write a DAX expression, e.g.: `HighValueSales = FILTER(Sales, Sales[Amount] > 1000)`

 

 3. Calculation Examples

 

1. Total Sales:

   - Measure: `Total Sales = SUM(Sales[Amount])`

2. Margin (%):

   - Measure: `Margin (%) = DIVIDE([Total Sales] - SUM(Sales[Cost]), [Total Sales])`

3. Monthly Sales Growth:

   - Measure: `Monthly Growth = [Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH(Dates[Date]))`

 

Using these basic DAX functions and calculations, you can make your data analysis and reporting more effective in Power BI.