Measure referencing other measures returns different result than when referencing local variables

[Karl-D]

The following DAX returns the correct values for result1 in all contexts, including the total.

For result2, the results are correct in the row context; for example if I create a visual with rows by 'Item'[Label]. 
However, the total amount does not match result1.

• result1 references other measures with the same exact definition as their VAR counterparts here.
• result2 uses the VARs defined here.

I obvisously am missing something with context, or perhaps need some CALCULATE?  However, I've tried adding CALCULATE to various places here, as well as in the standalone measures and it did not help.

How do I get result2 to produce the same number as result1 in all contexts?

Consolidated 9L ANA-1542(Consolidated) = 

VAR Consensus = 
    SUMX (
        FILTER(
            '_Item Budgets',
            RELATED('Item Budget Name'[Item Budget Status])="Under Development" ),
        '_Item Budgets'[Quantity 9L]
    ) 


VAR WSfinance = 
    SUMX (
        FILTER(
            '_Item Budgets',
                RELATED('Item Budget Name'[Item Budget Status])="Released" &&
                RELATED('Nav Department'[Dept Code])="20"
                ),
        '_Item Budgets'[Quantity 9L]
        )

VAR DTCfinance = 
    SUMX (
        FILTER( 
            '_Item Budgets',
                RELATED('Item Budget Name'[Item Budget Status])="Released" &&
                RELATED('Nav Department'[Dept Code]) IN { "35", "40", "45", "50" }
                ),
        '_Item Budgets'[Quantity 9L]
    )

VAR result1 = 
    SUMX(
        SUMMARIZE(
            '_Item Budgets',
            'Item'[Label],
            "xxx", IF(
                ISBLANK([Consensus]),
                [WSfinance],
                [Consensus]
            ) + [DTCfinance]
        ),[xxx]
    )

VAR result2 = 
    SUMX(
        SUMMARIZE(
            '_Item Budgets',
            'Item'[Label],
            "xxx", IF(
                ISBLANK(Consensus),
                WSfinance,
                Consensus
            ) + DTCfinance
        ), [xxx]
    )
    
RETURN
CONCATENATE(format(round(result1,2),"Standard"), CONCATENATE(" | ", format(round(result2,2),"Standard")))

[Karl-D]

Thank you both!  Accepted Owen since he was first, but both of your explinations and examples were appreicated, and work as expected.

[Anonymous]

Hi @Karl-D ,

result1 uses measures, such as [Consensus]. These measures are recalculated in the grouping context of SUMMARIZE, so they can adapt to the row context and total context.
result2 uses variables Consensus, WSfinance, and DTCfinance. The values of the variables are fixed (based on the current filtering context) when they are defined, and they are not dynamically recalculated in the subsequent SUMMARIZE grouping. Therefore, when the total row is calculated, the variables still use the original global aggregated values instead of dynamically adjusted values by group.

For details, please refer to:
Variables in DAX - SQLBI

To keep result2 consistent with result1 in all contexts, you need to embed the logic of variables into grouped calculations instead of relying on predefined variables.

Consolidated 9L ANA-1542(Consolidated) = 
VAR Result2_Fixed = 
    SUMX(
        SUMMARIZE(
            '_Item Budgets',
            'Item'[Label],
            "xxx", 
            VAR CurrentLabel = 'Item'[Label]  
            VAR Consensus_Group = 
                CALCULATE(
                    SUM('_Item Budgets'[Quantity 9L]),
                    '_Item Budgets'[Item Budget Status] = "Under Development"
                )
            VAR WSfinance_Group = 
                CALCULATE(
                    SUM('_Item Budgets'[Quantity 9L]),
                    '_Item Budgets'[Item Budget Status] = "Released",
                    'Nav Department'[Dept Code] = "20"
                )
            VAR DTCfinance_Group = 
                CALCULATE(
                    SUM('_Item Budgets'[Quantity 9L]),
                    '_Item Budgets'[Item Budget Status] = "Released",
                    'Nav Department'[Dept Code] IN { "35", "40", "45", "50" }
                )
            RETURN
                IF(
                    ISBLANK(Consensus_Group),
                    WSfinance_Group,
                    Consensus_Group
                ) + DTCfinance_Group
        ),
        [xxx]
    )
RETURN 
    xxxxx

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[OwenAuger · Accepted solution]

Hi @Karl-D 

It appears that the reason result2 is not working as expected is that variables are actually constants.

The variables Consensus, WSfinance, and DTCfinance are evaluated once when initially defined (in the existing filter context where the measure is evaluated), and their values are constant when they are referenced in SUMMARIZE within result2.

For for those three expressions to be re-evaluated for each 'Item'[Label] value, you need to either reference measures as you did in result1, or include the expressions within the SUMX iteration. You can still make use of variables, but they would need to be defined within the iteration.

A couple of other points:

• I would not recommend using SUMMARIZE to compute the extension column [xxx], but instead recommend either use ADDCOLUMNS or include the expression to be summed as the 2nd argument of SUMX (see this article).
• I personally suggest using SUMX with the expression to be summed in the 2nd argument, which requires the components of that expression to either be measures or to be wrapped in CALCULATE, so that the 'Item'[Label] value is applied as a filter.
• The expressions for Consensus, WSfinance, and DTCfinance can be written a bit more efficiently using CALCULATE/SUM/KEEPFILTERS rather than SUMX/FILTER/RELATED (see this article on filtering columns rather than tables).

Putting all this together, here is how I would consider writing the measure:

Option 1:

Write a single measure without relying on referencing those three measures:

Consolidated 9L ANA-1542(Consolidated) =
SUMX (
    SUMMARIZE ( '_Item Budgets', 'Item'[Label] ),
    VAR Consensus =
        CALCULATE (
            SUM ( '_Item Budgets'[Quantity 9L] ),
            KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Under Development" )
        )
    VAR WSfinance =
        CALCULATE (
            SUM ( '_Item Budgets'[Quantity 9L] ),
            KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Released" ),
            KEEPFILTERS ( 'Nav Department'[Dept Code] = "20" )
        )
    VAR DTCfinance =
        CALCULATE (
            SUM ( '_Item Budgets'[Quantity 9L] ),
            KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Released" ),
            KEEPFILTERS ( 'Nav Department'[Dept Code] IN { "35", "40", "45", "50" } )
        )
    VAR Result =
        COALESCE ( Consensus, WSfinance ) + DTCfinance
    RETURN
        Result
)

Option 2:

If you prefer to define Consensus, WSfinance and DTCfinance as separate measures, you could instead define the measures as:

Consensus =
CALCULATE (
    SUM ( '_Item Budgets'[Quantity 9L] ),
    KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Under Development" )
)

WSfinance =
CALCULATE (
    SUM ( '_Item Budgets'[Quantity 9L] ),
    KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Released" ),
    KEEPFILTERS ( 'Nav Department'[Dept Code] = "20" )
)

DTCfinance =
CALCULATE (
    SUM ( '_Item Budgets'[Quantity 9L] ),
    KEEPFILTERS ( 'Item Budget Name'[Item Budget Status] = "Released" ),
    KEEPFILTERS ( 'Nav Department'[Dept Code] IN { "35", "40", "45", "50" } )
)

Consolidated 9L ANA-1542(Consolidated) =
SUMX (
    SUMMARIZE ( '_Item Budgets', 'Item'[Label] ),
    VAR Consensus = [Consensus]
    VAR WSfinance = [WSfinance]
    VAR DTCfinance = [DTCfinance]
    VAR Result =
        COALESCE ( Consensus, WSfinance ) + DTCfinance
    RETURN
        Result
)

Do the above work in your model?

If not, I may have made some assumptions about the model that don't hold. In that case, could you provide more detail on the model and a model diagram or sample PBIX if possible?