Forum Discussion
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?
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 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:
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.
5 Replies
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:
David-Ganor Thanks a lot for your help 🙂
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 _FinalResultamitchandak Thanks a lot for reply. used your code but it still gives the wrong results. when using measure then it gives correct results.
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.
