Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anonymous_98
Resolver I
Resolver I

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

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? 
DAX Code - Incorrect result of Avg Profit Measure.PNG

 

 

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

 

 

 

DAX Code - Correct result of Avg Profit Measure.PNG

2 ACCEPTED SOLUTIONS
David-Ganor
Resolver II
Resolver II

@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

 

View solution in original post

anonymous_98
Resolver I
Resolver I

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.

Sales - Variable - Incorrect.PNG

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.

Sales - Variable - Correct.PNG

 

 

 

View solution in original post

5 REPLIES 5
anonymous_98
Resolver I
Resolver I

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.

Sales - Variable - Incorrect.PNG

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.

Sales - Variable - Correct.PNG

 

 

 

David-Ganor
Resolver II
Resolver II

@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

 

@David-Ganor Thanks a lot for your help 🙂

amitchandak
Super User
Super User

@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

 

 

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.