PBI CourseDAX-Pt3 What is calculate Function

In Power BI, the **CALCULATE** function is one of the most powerful and essential DAX functions.

What does CALCULATE do? CALCULATE evaluates an expression (like SUM, COUNT, AVERAGE, etc.) in a modified filter context.

In simple words:

*“CALCULATE changes or adds filters to the current calculation."*

💡 Comparing Total Sales with Main Branch Sales (e.g., Islamabad)

To compare total sales across different product categories with the sales of your main branch (e.g., Islamabad), follow these steps:

1. Select the main table in your Power BI data model.
2. Click on “New Measure” from the ribbon.
3. In the formula bar that appears, write the DAX expression for the main branch sales. For example:

Islamabad sales = CALCULATE(SUM(Data[Sales Amount]) , Data[Location] = "Islamabad")

Why is it important?

• It lets you override or add filters even when visuals already have their own.
• It’s crucial for time intelligence, conditional logic, and customized calculations.

This DAX formula:

• Calculates the total sales only for Islamabad.
• Overrides any external filters on the Location column.
• Ensures that Islamabad's sales appear consistently across each row, regardless of other filters in visuals.

Why is this meaningful? This approach is helpful when report viewers want to compare other cities or product category sales against a benchmark—like Islamabad’s sales. It provides a clear point of reference, making insights more meaningful and consistent across the report.

✅ This technique was also demonstrated in the previous article for better understanding.

Calculating Percentage Sales Across All Locations

The goal is to calculate the percentage of sales from a specific city compared to the total sales across all cities. To do this:

1. Create a new measure that calculates total sales across all cities, regardless of any filters applied.
2. Use the CALCULATE function with the ALL function to remove the filter from the Location column.

All Loc Sales = CALCULATE(SUM(Data[Sales Amount]), ALL(Data[Location]))

Explanation:

• SUM(Data[Sales Amount]): Sums up the sales amount.
• ALL(Data[Location]): Removes filters from the Location column, so you get the grand total of sales from all cities in the dataset.

The visualization looks something like this.

Now we need to calculate the percentage of total sales by location. To do this, we’ll create a new measure called Percent of Location Sales. In this measure, we'll divide the Total Sales (which we've already created) by the All Location Sales measure.

Percent of Location Sales = [Total sales] / [All Loc Sales]

The visualization looks something like this.

📂 Dataset & Power BI Files All related datasets and Power BI files are available here: 🔗 GitHub Repository