cancel
Showing results 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

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?

``````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``````

2 ACCEPTED SOLUTIONS
Resolver II

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

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.

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 5
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.

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.

Resolver II

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

Resolver I

@David-Ganor Thanks a lot for your help 🙂

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``````

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors