Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
120 | |
73 | |
72 | |
63 |