March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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?
so here is one way to fix this issue.
Sum of Internet Sales =
SUM('Internet Sales'[Sales Amount])
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?
so here is one way to fix this issue.
Sum of Internet Sales =
SUM('Internet Sales'[Sales Amount])
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:
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |