unexpected behavior of the DAX Code - using one variable inside another variable

[anonymous_98]

Hi,

This is the DAX Code which is givig unexpected/wrong results. so why this DAX code is giving wrong results? does anyone know this? 

Average Profit Per Transaction = 
    VAR _TotalProfit = 
        SUMX(
            'Internet Sales',
            'Internet Sales'[Sales Amount] - 'Internet Sales'[Total Product Cost]
        )

    VAR _FinalResult = 
        AVERAGEX(
            VALUES('Internet Sales'[Sales Order Number]),
            _TotalProfit
        )

    RETURN
        _FinalResult

so then what I did is, Took the formula of the _TotalProfit variable and created a new measure "Total Profit" as shown below. 

then used this measure "Total Profit" in place of variable "_TotalProfit" inside the measure "Average Profit Per Transaction".

Then it gave correct results as shown below.

Total Profit = 
    VAR _TotalProfit = SUMX(
        'Internet Sales',
        'Internet Sales'[Sales Amount] - 'Internet Sales'[Total Product Cost]
    )

    RETURN
        _TotalProfit

[David-Ganor · Accepted solution]

@anonymous_98 

DAX variable is a constant. Once assigned a value, the variable cannot be modified.

Variables are evaluated once in the scope of the definition (VAR) and not when their value is used.

Please review this video for deeper understanding:

https://youtu.be/71ERlpfhmwU

[anonymous_98 · Accepted solution]

Hey Everyone, 

after going through the video which @David-Ganor suggested, the concept of using variable became more clear. so here is an example for better understanding. 

so we are trying to calculate the Sales % here.

so we have created this measure "Sales - Variable - Incorrect" as shown below. but this measure is giving wrong result.

so why this measure this giving wrong result? lets find out?

• first the variable _CurrentSales is evaluated and is assigned a value, in our case it is _CurrentSales = 700759.96
• then variable _TotalSales is evaluated, here we are using the _CurrentSales variable to recalculate the Sales for ALL the Products irrespective of what the filter context is.
• so here is the catch, what we generally think is the variable _CurrentSales will be recalculculated for all the Products but this does not happen here. the value of the variable _CurrentSales remains the same in a given fitler context. so when _TotalSales is evaulated its values comes as, _TotalSales = 700759.96
• and thats why when we divide the _CurrentSales by _TotalSales, we get 100% as shown below.
• so how can we fix this issue? read further to know more.

so here is one way to fix this issue.

• while calculating variable _TotalSales
  • don't use existing variable _CurrentSales
  • create a measure which has same formula as _CurrentSales variable and then use that measure.
  • in our case, we have use measure "Sum of Internet Sales" having defintion/formula,

Sum of Internet Sales = 
    SUM('Internet Sales'[Sales Amount]) 

• and this will give you correct result.
• so this is how you can fix it.
• Below is the measure giving correct results.

[David-Ganor · Accepted solution]

@anonymous_98 

DAX variable is a constant. Once assigned a value, the variable cannot be modified.

Variables are evaluated once in the scope of the definition (VAR) and not when their value is used.

Please review this video for deeper understanding:

https://youtu.be/71ERlpfhmwU

[anonymous_98]

@David-Ganor Thanks a lot for your help 🙂

[amitchandak]

@anonymous_98 , if you use a expression in SumX/AverageX functions, you should use calculate or use a measure

Try like, bu better to use measure

Average Profit Per Transaction = 
    VAR _TotalProfit = 
        Calculated( SUMX(
            'Internet Sales',
            'Internet Sales'[Sales Amount] - 'Internet Sales'[Total Product Cost]
        )) 

    VAR _FinalResult = 
        AVERAGEX(
            VALUES('Internet Sales'[Sales Order Number]),
            _TotalProfit
        )

    RETURN
        _FinalResult

[anonymous_98]

@amitchandak Thanks a lot for reply. used your code but it still gives the wrong results. when using measure then it gives correct results.