What is Difference in these 2 average calculations

Coolpearl — Fri, 21 Feb 2025 19:52:31 GMT

Avg Sales= CALCULATE( AVERAGE(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] <> 0 ))

Avg Sales= CALCULATE( AVERAGE(TableName[SaleAmount]),TableName, TableName[SaleAmount] <> 0 )

giving me same results.

what is difference in 2?

basically I want avg of measure having non zero values , I don't want to consider rows with 0 as denominator count.

Re: What is Difference in these 2 average calculations

OwenAuger — Sat, 22 Feb 2025 02:45:52 GMT

Hi Coolpearl

Before discussing the difference between the two measures, here is how I would recommend writing this measure:

Avg Sales = CALCULATE ( AVERAGE ( TableName[SalesAmount] ), KEEPFILTERS ( TableName[SalesAmount] <> 0 ) )

This version of the measure applies the single-column filter TableName[SalesAmount] <> 0, intersected with any existing filters (due to KEEPFILTERS), then computes the average of SalesAmount.

Comment on expanded tables:

Whenever a physical table (i.e. a model table) is referenced in a DAX expression, it is treated by the engine as the "expanded table", which includes the table referenced plus columns of all tables on the 1-side of relationships with that table. The tables on the 1-side are joined to the table on the many-side. You can imagine a table constructed by left-outer-joining TableName to all related dimension tables on the 1-side of relationships with it (assuming a typical star schema).

Difference between the two original measures:

The two measures apply the same filtering logic in two different ways. I can't see any situation where they would return different results.

1. First measure

The first measure applies a single filter:

The physical table TableName (expanded table) evaluated in the existing filter context, then filtered row-by-row using the iterator FILTER, to include only those rows where TableName[SalesAmount] <> 0.

This measure may perform poorly because the expanded TableName table is materialized in memory and filtered row-by-row.

2. Second measure

The second measure applies two filters.

The physical table TableName (expanded table) evaluated in the existing filter context.
The boolean condition
```
TableName[SalesAmount] <> 0
```
which is translated into this:
```
FILTER ( ALL ( TableName[SalesAmount] ), TableName[SalesAmount] <> 0 )
```
This is a single-column table containing all nonzero values of SalesAmount (ignoring filter context).

These two filters are applied simultaneously, so effectively "intersected". Since the set of all nonzero values of SalesAmount (from 2nd filter) is a superset of the values of SalesAmount in TableName in the existing filter context (from 1st filter), any rows of TableName where SalesAmount <> 0 are excluded, giving the same result as the first measure.

Potential unexpected results from both of these measures

In both of the original measures, the calculation performed in the first argument of CALCULATE is relatively simple:

AVERAGE ( TableName[SalesAmount] )

As a result, in "typical" situations, the fact that the expanded table is used within CALCULATE's filter arguments will usually not result in any unexpected results. The measure returns the average of SalesAmount within the existing filter context, excluding any zero SalesAmount values. Even if you have, say, a filter in your report on SalesValue itself (such as SalesValue >= 100), both measures would still return the expected average of nonzero values within that filter context.

However, there are situations where applying these kinds of filters could result in unexpected results:

An existing "complex filter" involving columns both within and outside the expanded TablleName table.
A complex filter (or arbitrarily-shaped set) is a filter consisting of multiple columns that is not an intersection of single-column filters. For example, "Red Products in 2017 or Green Products in 2018". If you had such a filter involving say a column within the expanded TableName table and a column outside the expanded TableName table, either of the original measures could "break" the relation between the columns expressed in the complex filter (complex filter reduction). To be honest, this would have to be a pretty contrived situation. It could come about if a table involved in the complex filter had many-to-many relationships with Sales (since tables related by many-to-many relationships are not included in expanded tables).
Performing a more complex calculation involving multiple columns, for example sales per day.
Take this measure for example:
```
Sales per Day =
CALCULATE (
    DIVIDE (
        SUM ( TableName[SalesAmount] ),
        COUNTROWS ( 'Date' )
    ),
    FILTER ( TableName, TableName[SalesAmount] <> 0 )
)
```
Assuming 'Date' is on the 1-side of a 1-to-many relationship with TableName, the 2nd argument of CALCULATE would limit the visible rows of 'Date' to just those related to rows of TableName where SalesAmount <> 0. If you have filtered 'Date' to a 7-day period and nonzero SalesAmount values occurred only on 3 of those days, COUNTROWS ( 'Date' ) would return 3 rather than 7.

Conclusions:

The two original measures apply the same filtering logic, and should return the same results in all scenarios.
1. The first measure filters TableName (expanded table) row-by-row with the condition SalesAmount <> 0.
2. The second measure applies TableName (expanded table) as a filter, and simultaneously applies all nonzero values of SalesAmount as a filter. The combination of these gives the same filtering result as the first measure.
However, as a general rule, it is best to filter columns, not tables, because of
1. The unexpected consequences of filtering expanded tables.
2. Better performance when filtering columns, since the expanded table need not be materialized.
  See this article.
If you want to apply filters while retaining existing filters (i.e. intersected with existing filters), wrap the filter(s) within KEEPFILTERS.
This ensures that filters are robust to any existing complex filters that might have been applied.
See this article.
You may see no difference between column vs table filters in "simple" situations, but I believe it is best to write measures that are robust and will return correct results in all situations (which can't always be anticipated in advance). The linked articles cover this in much more depth 🙂

Hope that helps!

Re: What is Difference in these 2 average calculations

quantumudit — Sat, 22 Feb 2025 02:42:03 GMT

Hello Coolpearl

1️⃣ Using FILTER() inside CALCULATE()

Avg Sales = CALCULATE( AVERAGE(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] <> 0 ) )

FILTER(TableName, TableName[SaleAmount] <> 0) returns a filtered table where SaleAmount is not 0.

CALCULATE() then applies this filtered table to the AVERAGE() function.

Since FILTER() creates a row context, it allows complex filtering, such as using additional conditions or calculations.

2️⃣ Passing Table and Condition Directly in CALCULATE()

Avg Sales = CALCULATE( AVERAGE(TableName[SaleAmount]), TableName, TableName[SaleAmount] <> 0 )

CALCULATE( AVERAGE(TableName[SaleAmount]), TableName, TableName[SaleAmount] <> 0 ) applies a direct filter condition (TableName[SaleAmount] <> 0).

This is more efficient because CALCULATE() internally converts TableName[SaleAmount] <> 0 into a filter over TableName without needing FILTER().

However, this direct filtering only works with simple column filters and does not support row context like FILTER().

If you need complex row-by-row filtering (e.g., comparing different columns, calculating on the fly, etc.), you must use FILTER().

If the condition is simple (like TableName[SaleAmount] <> 0), passing it directly is preferred

For your case, both return the same result because the condition is straightforward. However, using FILTER() is unnecessary overhead in this scenario. The second approach is more efficient and should be preferred unless you require row context for advanced filtering.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

Re: What is Difference in these 2 average calculations

Coolpearl — Mon, 03 Mar 2025 17:42:41 GMT

thank you audit and Owen for detail response. Very much helpful.

topic Re: What is Difference in these 2 average calculations in DAX Commands and Tips

What is Difference in these 2 average calculations

Re: What is Difference in these 2 average calculations

Re: What is Difference in these 2 average calculations

Re: What is Difference in these 2 average calculations