DAX HANDBOOK
2.3 Columns vs Measures (Implicit vs Explicit Measures)

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

When first getting acquainted with DAX and modeling, it’s confusing when to create calculation logic in a Calculated Column and when to use Measures. Quite often novice users try to push all calculations to Calculated Columns, then drag them to the Values field of the visual (making an implicit Measure) or performing a simple Explicit measure (e.g. SUM(CalculatedColumn)) just to return the value back to the visual. This approach is fundamentally wrong. Calculation logic should almost always be implemented in a Measure, and calculated columns should be used only as a last resort and mostly in case of creating static Filter types of columns. We will try to explain best practices with the following example of calculating Gross Margin % in our Sales table.

Calculated Columns Approach

First, we calculated all the figures using only calculated columns.

SalesAmount = Sales[Price]*Sales[QTY] 
COGS = Sales[QTY]*Sales[Cost]
GM Column = Sales[SalesAmount]-Sales[COGS]
GM % = DIVIDE(Sales[GM Column],Sales[SalesAmount])

Since we operate in a row context, for each row of the table the calculations return correct figures. But the idea of Power BI/Excel is to present the figures in a visual. We might try to add those columns to the Matrix visual and create implicit Measures.

Although [SalesAmount], [COGS] and [GM Column] implicit Measures seem to return the right figures, the GM % is obviously wrong. We know as a fact that GM% cannot be above 62.6%, as seen if we sort the GM% column descending.

The GM% should fall somewhere under 62.6% and cannot be above the mentioned figure. The problem is that we created an implicit measure using the Value column [GM%]. When creating implicit measures with Value columns, the default aggregation of the data is SUM.

If we focus on the first row in the visual, which is filtered by the “Black” color, the following occurred.

  1. Original filter context filtered the Sales table so that only Black products survive.
  2. After the original context has filtered the Sales table, the Implicit calculation kicks in and sums all the values in the GM% column which survived the filtering.
  3. That value is returned under the Black color field in a Visual.

The issue is obvious. Even though the GM% is correct for each row in the table, it is completely wrong when used in Visual, or to be precise, in the filter context. You might try to change the aggregation type of the implicit measure to Average, but this again is not the correct number since this number is a non-pondered average.

Measures Approach

The only logical way to present the correct GM% result would be to use an Explicit measure. If we observe the row in a visual filtered by the “Black” color, the correct GM% would be the division of numbers 14.010.650 and 33.702.965 which equals 41.6%, not 46.2% as with the non-pondered average.

Now let’s see how to create a Measure that always returns the correct GM% under any filter context.

  1. Original filter context filtered the Sales table so that only Black products survive.
  2. After the table is filtered by the original filter context, we need to divide the sum of [SalesAmount] column with the sum of [GM column] column. We have to use the SUM aggregation function to sum values of multiple rows in a single, scalar value that can be represented in a visual.
  3. To return the correct GM% in a visual we need to use an explicit measure with the formula DIVIDE(SUM(Sales[GM Column]),SUM(Sales[SalesAmount]). In our model, we named that measure [GM% Measure].

We used 2 Calculated Columns in creating the [GM% Measure] measure. As we’ve already learned, we should only add a calculated column to a model as a last resort. In all other situations, we should lean towards Measures.

Can we use Measures to produce SalesAmount? If we observe the SalesAmount calculated column, we can see that it is operating in a row-by-row calculation mode (row context). Until now all Measures we used operated in a filter context, aggregating multiple rows in a single scalar value. If we try adding a Measure with the same formula as in the Calculated column, we will receive the error message.

If we try to add the SUM function to force scalar value, the figures are again not correct.
Measure SalesAmount = SUM(Sales[Price])*SUM(Sales[QTY])

We are now multiplying a sum of Price column with the sum of QTY column, which gives us a huge incorrect number.

To return a correct SalesAmount, we need to use a Measure that can introduce row-by-row calculation, then SUM the result of the iteration. There is a special type of DAX functions for that purpose, called iterative functions. We will explore them in the following chapter.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top