Error in SUMX measure

[Syndicate_Admin]

Good morning.

I have this average, where I intend to make the operation of quantity x the average price. If you look at the column called "columana" it is a calculated column and it is the one that gives me the correct calculation, but the measurement multiplies it by 2, do you know why?

Total Component Cost =

SUMX ('TodosRebajesconfecha', 'TodosRebajesconfecha'[xcant_reb] * TodosRebajesconfecha[xprec_med_coste])

[Syndicate_Admin]

Thank you for replying.

The problem I have is with the average price field, as you can see, the measure M_xprec_med_coste_Total, is the one that is duplicating this field, the column "Average price" is the correct one, the problem is when using the measure "M_xprec_med_coste_Total"

[v-jtian-msft · Accepted solution]

Hi,@Fer20201 , Hello,@lbendlin ,

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

According to your description, you use the same Dax code in calculate column and measure, but you get different results, where measure gives *2 results.

This is because in measure and calculate column, their computational environment is different.

The calculation environment of measure is the filtering context:

Filtering context is the context (calculation range) defined by a filtering condition created by the user or another Dax function

measure in the current filtering context refers to the calculation of a time

While field filtering by filter or Silcer, depending on the filtering conditions and dax code will affect the result of the measure, i.e., measure is a dynamically changing scalar (single value).

The environment for calculating colum is the row context.

Row context simply means the current row, and calculate column will provide a context for each current row to calculate the result.

Calculated columns are calculated during data refresh and have static values.
Here is the test I performed
The code is as follows:

M_ = 
SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[quantity]*'TodosRebajesconfecha'[discounted price])

C_ = 
SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[quantity]*'TodosRebajesconfecha'[discounted price])

The current context can be changed using the calculate function: the row context is changed to the filtering context
 like this:

C_2 = 
 CALCULATE(SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[quantity]*'TodosRebajesconfecha'[discounted price]))

M_2 = 
 CALCULATE(SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[quantity]*'TodosRebajesconfecha'[discounted price]))

The results of the test are as follows:

Without using the calculate function, C_ performed 'TodosRebajesconfecha'[quantity]*'TodosRebajesconfecha' for each row, and eventually performed a summation of the values of each row, returning the total number of discounted sales of all products, and this result was not affected by the external filtering This result is not affected by external filtering conditions, and is always the constant value of 37535.24.

After the use of the calculte function, the environment of the calculation changes, C_2 becomes the same as the measure, calculates the correct amount of discounted sales for each product, and will be affected by the filtering criteria

You can check your code by following my suggestions below

1. Check the computational environment of the DAX code you are using.
2. Use the laculate function to modify the computational environment.

3. You can break your current measure into smaller parts, e.g. by using SUMX to calculate the value of a column separately first.

For example, break it down into the following two measures:

M_xcant_reb_Total = SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[xcant_reb])

M_xprec_med_coste_Total = SUMX('TodosRebajesconfecha','TodosRebajesconfecha'[xprec_med_coste])

Determine where the differences in calculations are by comparing their results with the previous code.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...