DAX Nested Aggregate Contexts/Filters

Question

I'm trying to write a DAX calculated measure which uses nested aggregates to perform a calculation based on the user specified query context, and I'm having trouble figuring out how to apply the query context to the inner aggregation as well as the outer. The simplified structure of my tabular data is below, where each Sale record represents the sale of a single widget by one user to another for the given sale price:

Schema

And some sample data:

Data

The calculated measure query itself is below, which is essentially attempting to calculate on average how good of a bargain each buyer gets for their purchases. It does this by performing the following calculations:

1) Inner 'Calculate': For each Sale record, Calculate the average Price the given Seller sells their widgets for (potentially filtered).

2) Outer 'Calculate': For each Sale record, Calculate the average of the Price minus the amount calculated in #1, essentially giving a differential in actual vs expected sale amounts.

Avg Actual/Expected Differential :=
    CALCULATE (
        AVERAGEX (
            Sale,
            Sale[Price]
                - CALCULATE ( AVERAGEX ( Sale, Sale[Price] ), ALLEXCEPT ( Sale, Sale[Seller] ) )
        )
    )

This formula works in the standard case where no query filters are being applied but User related filters. For example, the actual vs expected sales to Dale are:

• Sale from Larry for $2, where Larry's average sale is $3.5. Differential = -$1.5
• Sale from Bob for $5, where Bob's average sale is $3. Differential = $2
• Sale from John for $4, where John's average sale is $4. Differential = $0

Thus, the Avg Actual/Expected Differential is $.5 / 3 = $.17.

The problem I'm running into is applying a query filter on the IsCashSale field and having that filter apply to both the inner and outer Calculate functions.

For example, if I want to filter both inner and outer to only include Sale records which have a True IsCashSale value I can create the appropriate filter in the UI and the outer Calculate function is filtered appropriately, however when the inner Calculate runs, it removes this filter on IsCashSale as a result of the AllExcept function which removes the filters from all columns except the Seller field.

I tried including the Sale[IsCashSale] amount in the AllExcept field list, however because of the current row context, this only includes all records which have the same IsCashSale amount value as the current record of the inner 'Calculate' loop. This causes the scenario where no filter has been applied to IsCashSale to be incorrect.

I believe I may be looking for a way to pass the selected Values from the outer Calculate to the inner one? If I can do that, I can include those values as filters on the inner Calculate. Is there a way to use something like the Earlier or AllSelected functions to do this? Or is there another way to do what I'm attempting to do? Thanks!

Answer 1

How about using the ISFILTERED function?

Avg Actual/Expected Differential 2:=IF(ISFILTERED([IsCashSale]),CALCULATE (
    AVERAGEX (
        Sale,
        Sale[Price]
            - CALCULATE ( AVERAGEX ( Sale, Sale[Price] ), ALLEXCEPT ( Sale, Sale[Seller],Sale[IsCashSak]))
    )
),
CALCULATE (
    AVERAGEX (
        Sale,
        Sale[Price]
            - CALCULATE ( AVERAGEX ( Sale, Sale[Price] ), ALLEXCEPT ( Sale, Sale[Seller]))
    )
)
)

Now you will see the results like this when the IsCashSale column is filtered, and when it is not filtered it will behave the original way.

Are these the results that you wanted?

+----+--------+-------+-----------+--------------+------------------------------------+
| Id | Seller | Buyer | IsCashSak | Sum of Price | Avg Actual/Expected Differential 2 |
+----+--------+-------+-----------+--------------+------------------------------------+
|  1 | Bob    | John  | TRUE      |            1 |                                  0 |
|  2 | John   | Bob   | TRUE      |            2 |                                 -1 |
|  3 | Dale   | Bob   | TRUE      |            1 |                               -0.5 |
|  8 | Sue    | Bob   | TRUE      |            3 |                                  0 |
| 10 | John   | Dale  | TRUE      |            4 |                                  1 |
| 13 | Dale   | Kelly | TRUE      |            2 |                                0.5 |
+----+--------+-------+-----------+--------------+------------------------------------+

Answer 2

Ok, I think I've found a solution, but it's a bit convoluted. It requires breaking out the sum of the individual purchases by the buyer from the average sales for each seller. I believe the big difference was that by using the VALUES(Sale[Seller]) set definition rather than just Sale it allows us to keep whatever filters are in place on the IsCashSale field in the outer Calculate function:

Avg Actual/Expected Differential:=CALCULATE(
(
    SUM(Sale[Price]) --Sum of Buyer purchases
    - CALCULATE (   
        SUMX( 
            VALUES(Sale[Seller]) --Calculate for each unique Seller
            ,CALCULATE(COUNTROWS(Sale)) --Need to multiply by number of purchases from this particular Seller
            * CALCULATE ( --Get Seller's average sale price
                AVERAGEX (Sale, Sale[Price])
                ,ALLEXCEPT(Sale, Sale[Seller], Sale[IsCashSale])
            )
        )
    )
)
/ COUNTROWS(Sale) --Divide by total number of sales to get average

)