Evaluation Context and Calculating Sales Margins

This post is a continuation of Part 3 in our blog series “Excel to Power BI”. In our last post we started writing basic DAX formulas while touching on core concepts including Row and Filter Context. In this post we continue writing DAX and will work through different approaches of writing DAX expressions with the end goal of properly reporting margin dollars and margin percentage.

As a reminder, this post is intended to give the Excel user just a sample of writing basic DAX formulas. There are plenty of books and courses available that provide formal DAX education. Feel free to check out our learning resource page to browse some of the best the community has to offer.

Lets review what we’ve learned so far about Evaluation Context:

“Evaluation Context identifies the current environment of our value, then works with our tables to process and return the correct result,”

When DAX identifies the environment of our value, it then chooses between one of two Evaluation Contexts, Filter Context or Row Context.

• Filter Context applies to values that sit in a report visual like a Pivot Table. DAX evaluates the context of surrounding report filters (I.e. row and column labels) that define each value, then filters our tables accordingly. With only relevant table rows remaining, DAX finally evaluates the formula expression retrieving the updated value.

• Row context is used on values that sit in the rows of our data tables, similar to an Excel sheet that supports a Pivot Table. Adding new calculated columns often requires row by row calculations. In Power BI, we refer to this evaluation as Row Context, and the act of stepping through each row as Table Iteration.

To learn more about Evaluation Contexts, click here.

In this post we continue working with measures and calculated columns, and will illustrate how to explicitly call on Row Context within a measure and Filter Context within a calculated column. This flexibility will allow us to provide improved insights for our report readers.

Let’s start with calling on Row Context within a Measure:

Example 3: Iterators

“We use iterator functions within a measure to help mirror the row by row behavior we see by default in calculated columns”

In our last post we learned in Example 1 how to write a calculated column in our table. It is important to note that the result in our original calculated column only refreshes when the whole model refreshes.

If we had followed best practice, we would have chosen to write this calculated column as a measure instead of a physical column. By doing so, we’d maintain a physically smaller and more efficient data model. However, unlike calculated columns, measures update each time the user interacts with the report visuals.

By using an iterator like the SUMX function, we signal to DAX that we require our expression to calculate one row at a time. To accomplish this, DAX requires that we pass through all the elements needed:

1. We identify a table or table expression in the first argument,

2. We write an expression that evaluates row by row over our table in the second argument,

3. Finally we wrap it all within an iterator function like SUMX (or other X functions like AVERAGEX, MAXX, RANKX, etc.) to instruct DAX that we want our measure to aggregate our result with a sum over all of the pre-calculated rows.

SUMX Example = SUMX(Table, Table[Qty]*Table[Price])

Lets open up our Stepping Stone sample file and see where we left off from the last post. Download the sample file below:

Now lets create a new measure in our SteppingStone table that will use our new SUMX function:

Write the following formula in a new Measure:

Next lets drag this new measure into our existing report table:

When we place our measure into our report table visual, we see that our SUMX measure lines up perfectly with our Example 1 Calculated Columns result.

We’re at a good place to point out that anything we use in a report visual, regardless of originally using Row or Filter Context in the evaluation, ultimately evaluates to filter context when seen from our report visual. It’s the original filter Context of our visual that helps ensure the correct result.

This is a very simple explanation and there is much more involved to fully understand the order of steps that take place. To learn more, click here.

Example 4: Using CALCULATE in a Calculated Column

“CALCULATE allows us to generate a filter context within a Calculated Column using ‘Context Transition’.”

We’ve just seen how we can create a Row Context in a Measure that ultimately evaluates under Filter Context. Now lets demonstrate creating a calculated column that instead of using row context by default, uses filter context instead.

Imagine we need a new physical column in our Part table to calculate total sales for each product Class. We need the total sold amount in order to define a High or Low selling product class. We need a calculated column here to produce a report slicer to filter our report between High and Low selling product classes.

To emphasize our need for this function, lets first attempt this calculated column by using the SUMX() function.

Create a new calculated column:

Write the following Calculated Column:

As you see in the result above, each row in our column totals the sum of all sales in our Sales table. Even though our Part table is directly related to our sales, and we call on an iterator, we are still not seeing that filter behavior by product class that we expected.

We need a function that isolates each unique row in the Part table, and signals DAX to filter our tables using just the information found in each row.

To accomplish this, we are going to introduce and just scratch the surface of what the CALCULATE function can do for us.

CALCULATE is one of the most powerful and widely used functions in DAX. It gives us the ability to modify our expression by adding, removing, and overwriting filters as needed. It also converts any row context provided into an equivalent filter context, which is the goal in this example.

CALCULATE:

1. Reads all incoming filters and any row context provided,

2. Converts the incoming row context into new filter context and gets rid of the old row context,

3. Uses the new collection of filters to filter our tables,

4. Applies any special modifications we may have asked for in our CALCULATE formula,

5. Once everything is read and filtered, our expression evaluates over the remaining table rows and returns the resulting value back to the original report cell.

Let’s try our calculated column using CALCULATE and see what happens.

Here we see the difference that the CALCULATE function makes in our result. By wrapping our SUM() in a CALCULATE() function, we ask DAX to forget about row by row context evaluation, and instead use the row values as filters over our tables. The final result is a sum aggregation over all like rows that match the values found in each row of our table.

Technically this Row-to-Filter Context conversion is called Context Transition, and the act of filtering our tables in this way is referred to as Equivalent Filter Context. This process is powerful but should be used with caution.

It is best to use this process in smaller surrounding tables (Parts, Customer, Location) on the ‘one side’ of the relationship where you are sure to avoid duplicate table rows. If you can’t be sure that each row contains at lease one unique value, you run the risk of overstating your results.

For example, say we have two identical rows containing the value 5. The first Row filters our table and sees two rows matching our Filter requirement, the first row expression returns 10 (5+5). This may be correct for the first row, but then this repeats again for the second duplicate row. The 10 in the second expression is a duplicate result, as it includes the same row values (5+5). We further exaggerate the results at the total line in our report visual when both results combine to total 20!

Convert our Calculated Column into a High / Low selling product flag:

Now that we’ve proven that our calculated column can safely use filter context from the Part table, lets create another calculated column using this same code to determine our High and Low selling product, then use this column in a report slicer.

Here are a few tips before we create our new column:

1. To expand the formula bar click on the down arrow at the very far right of the formula bar.
2. In order to write your DAX over multiple lines, you will need to hold down the Shift key and press Enter at the same time in order to move down to the next line in your formula bar.
3. The key word “VAR” must preceed your variable name and expression.
4. The keyword “RETURN” must preceed the final variable that will return your final result.
5. By using the “—” characters, you are able to add text comments that are not part of your code.
6. Variables are only apply within the DAX formula you are writing. They can not be used in other measures or columns.
7. In complex DAX, variables allow us to break our code up into logical steps, and at times help us eliminate repetitive calculations. In more advanced DAX it allows us to combine contexts from different parts of our formula more efficiently.

Here is the code to use to create our High and Low column:

Now for our final step, lets create a slicer in our report that will allow us to switch between reporting on High and Low selling product. Remember, the sole purpose of using a calculated column, was that it allowed us to create a report slicer with the result.

By choosing HIGH we filter our report to show only higher selling products selling at or above $200. By choosing low selling product, we see that the product Class Screws are our lower selling products.

Conclusion

In this post we’ve continued our DAX examples by learning how specific DAX functions (SUMX and CALCULATE) can be used within our DAX to call on specific evaluation contexts. In the case of SUMX we were able to create a row context within a measure. With CALCULATE we were able to create a filter context in a calculated column that would normally use row by row context to evaluate one row at a time.

Best practice is to use a measure over a calculated column whenever possible. Choosing measures over calculated columns improves the physical size of our tables, and leaves us with a more efficient model. However, one valid reason to use a calculated column is to use the resulting values in a report slicer to filter your report.

In the next post, we are going to walk through how we share our reports in the Power BI cloud, referred to as simply “The Service”. Sharing our report in the service is where the real flexibility and automation of our efforts really start to pay off!

As always, thanks for reading! – Mark