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

Held PowerApps training for Žito d.d.

Last week we went to Osijek to hold a PowerApps training for Žito d.d. The training took place at the client’s office, lasting 2 days, with a total duration of 8 hours. The participants of the training have been using PowerApps in their business for some time, but they wanted...

Read more

Održana PowerApps edukacija za Žito d.d.

Prošli tjedan svratili smo do Osijeka kako bismo održali PowerApps edukaciju za Žito d.d. Edukacija se održala u 2 dana kod klijenta u uredu, u sveukupnom trajanju od 8 sati. Polaznici edukacije već neko vrijeme koriste PowerApps u svom poslovanju, no željeli su usavršiti svoje vještine u samostalnoj izradi aplikacija,...

Read more

This year’s first PowerApps training for TÜV NORD Adriatic

Last week we held this year’s first PowerApps training for our long-term clients from the TÜV NORD Adriatic company. It was a full-day education lasting 6 hours, led by our PowerApps expert and developer Kristian! The PowerApps platform is intended for the creation of customized business applications and enables the...

Read more

Prva ovogodišnja PowerApps edukacija za TÜV NORD Adriatic

Prošli tjedan održali smo prvu ovogodišnju PowerApps edukaciju za naše dugogodišnje klijente iz TÜV NORD Adriatic kompanije. Bila je to cjelodnevna edukacija u trajanju od 6 sati, koju je vodio naš PowerApps stručnjak i developer Kristian! PowerApps platforma namijenjena je izradi prilagođenih poslovnih aplikacija i omogućava brzi razvoj aplikacija koje...

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