In Power BI, you can perform advanced calculations and analysis on your data by using custom measures and calculated columns. Below are the steps to create custom measures and calculated columns, along with some examples of their usage.
.jpg)
Using Custom Measures and Calculated Columns in Power BI
1. Custom Measure
Usage: Custom measures are used for various types of
numerical analysis, such as total sales, average values, or custom calculations
that adjust according to different parameters in your report.
How to Create:
1. Open Power BI
Desktop:
- Navigate to your
Power BI report.
2. Create a New
Measure:
- Go to the
"Modeling" tab and select "New Measure."
3. Write a DAX
Expression:
- Name the measure
and write a DAX expression. For example, to create a measure for total sales:
```DAX
Total Sales =
SUM(Sales[Amount])
```
4. Use the Measure:
- Use the newly
created measure in visuals like charts or tables in your report.
Examples:
- Total Sales:
```DAX
Total Sales =
SUM(Sales[Amount])
```
- Margin (%):
```DAX
Margin (%) =
DIVIDE(SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]), 0)
```
- Monthly Sales
Growth:
```DAX
Monthly Growth =
[Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH(Dates[Date]))
```
2. Calculated Column
Usage: A calculated column is used to create custom
calculations for each row in a table. It appears as part of the main table and
can be used in reports, filters, or visuals.
How to Create:
1. Open Power BI
Desktop:
- Navigate to your
Power BI report.
2. Create a New
Column:
- Go to the
"Modeling" tab and select "New Column."
3. Write a DAX
Expression:
- Name the column
and write a DAX expression. For example, to calculate the profit margin per
sale:
```DAX
Profit Margin =
Sales[Amount] - Sales[Cost]
```
4. Use the Column:
- Use the newly
created column in your report visuals, such as tables or charts.
Examples:
- Total Profit:
```DAX
Total Profit =
Sales[Amount] - Sales[Cost]
```
- Sales Tax:
```DAX
Sales Tax =
Sales[Amount] * 0.10
```
- Discounted Price:
```DAX
Discounted Price =
Sales[Amount] * (1 - Sales[Discount Rate])
```
3. Using Custom Measures
and Calculated Columns
1. Data Analysis:
- Custom measures
and calculated columns help analyze various aspects of your data. For example,
by calculating margins, you can analyze profit levels.
2. Report Design:
- Use custom
measures and columns to create more detailed and insightful visuals for your
reports.
3. Filters and
Slicers:
- Custom measures
and columns allow you to create filters and slicers, helping you focus on
specific parts of your data.
4. Tips
1. Performance
Considerations:
- Calculated
columns are stored as part of the table, so in large tables, they can affect
performance. Measures, however, are calculated in real-time during reporting,
making them generally more efficient.
2. Debugging:
- If there are
errors in your DAX expression, Power BI will display an "Error"
message. To ensure correct calculations, validate your expressions and, if
needed, use tools like "Evaluate."
By following these steps, you can create custom measures and
calculated columns in Power BI to enhance the analytical power of your reports.
In Power BI, you can perform advanced calculations and
analysis on your data by using custom measures and calculated columns. Below
are the steps to create custom measures and calculated columns, along with some
examples of their usage.
1. Custom Measure
Usage: Custom measures are used for various types of
numerical analysis, such as total sales, average values, or custom calculations
that adjust according to different parameters in your report.
How to Create:
1. Open Power BI
Desktop:
- Navigate to your
Power BI report.
2. Create a New
Measure:
- Go to the
"Modeling" tab and select "New Measure."
3. Write a DAX
Expression:
- Name the measure
and write a DAX expression. For example, to create a measure for total sales:
```DAX
Total Sales =
SUM(Sales[Amount])
```
4. Use the Measure:
- Use the newly
created measure in visuals like charts or tables in your report.
Examples:
- Total Sales:
```DAX
Total Sales =
SUM(Sales[Amount])
```
- Margin (%):
```DAX
Margin (%) =
DIVIDE(SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]), 0)
```
- Monthly Sales
Growth:
```DAX
Monthly Growth =
[Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH(Dates[Date]))
```
2. Calculated Column
Usage: A calculated column is used to create custom
calculations for each row in a table. It appears as part of the main table and
can be used in reports, filters, or visuals.
How to Create:
1. Open Power BI
Desktop:
- Navigate to your
Power BI report.
2. Create a New
Column:
- Go to the
"Modeling" tab and select "New Column."
3. Write a DAX
Expression:
- Name the column
and write a DAX expression. For example, to calculate the profit margin per
sale:
```DAX
Profit Margin =
Sales[Amount] - Sales[Cost]
```
4. Use the Column:
- Use the newly
created column in your report visuals, such as tables or charts.
Examples:
- Total Profit:
```DAX
Total Profit =
Sales[Amount] - Sales[Cost]
```
- Sales Tax:
```DAX
Sales Tax =
Sales[Amount] * 0.10
```
- Discounted Price:
```DAX
Discounted Price =
Sales[Amount] * (1 - Sales[Discount Rate])
```
3. Using Custom Measures
and Calculated Columns
1. Data Analysis:
- Custom measures
and calculated columns help analyze various aspects of your data. For example,
by calculating margins, you can analyze profit levels.
2. Report Design:
- Use custom
measures and columns to create more detailed and insightful visuals for your
reports.
3. Filters and
Slicers:
- Custom measures
and columns allow you to create filters and slicers, helping you focus on
specific parts of your data.
4. Tips
1. Performance
Considerations:
- Calculated
columns are stored as part of the table, so in large tables, they can affect
performance. Measures, however, are calculated in real-time during reporting,
making them generally more efficient.
2. Debugging:
- If there are
errors in your DAX expression, Power BI will display an "Error"
message. To ensure correct calculations, validate your expressions and, if
needed, use tools like "Evaluate."
By following these steps, you can create custom measures and
calculated columns in Power BI to enhance the analytical power of your reports.
.jpg&description= Using Custom Measures and Calculated Columns in Power BI - AnalysisWithSadiul){kind=link}