Utilizing DAX (Data Analysis Expressions) to create custom aggregations in Power BI empowers you to define calculations that are exceptional for your data analysis. Here’s the easiest way to do it:
Step 1: Get to Know Your Data Model
Writing DAX formulas without any knowledge of your data model is not advised. You must know the tables you will be using as well as the lines linking them and their relations to each other. This understanding is very important when you want to create useful aggregates.
Step 2: Create a New Measure
Open Power BI Desktop and navigate to either the Data view or the Report view.
In the Fields pane, right-click on the table where you want to create the new measure and select New Measure.
Step 3: Write Your DAX Formula
So, in the bar below, you may enact any custom function as an aggregation defined in DAX. Suppose you want to build this in addition to the measure that calculates the total sales for all products, but the electronics category of products is excluded. Your formula would look something like this:
Total Sales for Category =
CALCULATE(
SUM(Sales[Amount]),
Products[Category] = "Electronics"
)
SUM(Sales[Amount]): This calculates the total sales amount from the sales table.
CALCULATE(...): This function alters the filter context of the calculation. It allows one to apply the filters to the data.
Products[Category] = “Electronics”: This condition filters the data only to include the sales for the “Electronics” category.
Step 4: Employ Your Measure.
After creating the measure, you can incorporate it into your reports and visualizations. Drag it into your report canvas, and it will display the custom aggregation that you created.
