Introduction
Welcome to Excel Jet Consult blog post. In this blog, I will walk us through the concept of forcing additive measure in Power BI. Let’s get started
Understanding Additive vs. Non-Additive Measures
In Power BI, measures can be categorized as additive or non-additive based on how they aggregate when multiple data points are combined. Additive measures are those that can be summed up across different dimensions, such as time, geography, or product categories, and still provide meaningful results. Non-additive measures, on the other hand, cannot be summed without losing context or creating inaccuracies.
Common examples of additive measures include sales revenue, profit, and quantity sold, as they make sense to aggregate across various dimensions. Non-additive measures, like averages, ratios, or percentages, are not inherently additive.
Forcing Additive Measures
There are situations where you may want to force a non-additive measure to behave as an additive measure in Power BI. This can be particularly useful when creating complex calculations or custom aggregations. To force a measure to be additive, you need to employ DAX (Data Analysis Expressions) functions effectively.
Practical Example
In the screenshot below, we have a simple sales report in the report canvas. To create the report, we dropped the Region, explicit DAX measure that calculates Total Sales Amount as well as No of Product. The No of Product measure is calculated using the DISTINCTCOUNT based on the ProductKey column in the FactInternetSales table. However, we observed that the Total for the No of Product is clearly incorrect – we expect to see 631 as the correct Total instead of the 158. The DISTINCTCOUNT in this case is non-additive.
We want to force additive to the No of Product measure to return accurate Total. To do that, we are going to create a new measure called No of Product with Additive.
- So, right-click on any of the measures or column under the FactInternetSales and select New Measure
Execute the DAX Measure:
No of Product with Additive = SUMX( VALUES(dimRegion[Region]), [No of Product] )
- Click Enter to commit and apply comma separator.
- Drag and drop the newly created measure into the report.
In the the screenshot above, we can see that the we have the correct Total of the No of Product. In conclusion, forcing additive behaviour for non-additive measures in Power BI is important to perform calculations and aggregations that would be otherwise challenging. See you in the next blog