Measure works at line level but returns wrong result on total

[Anonymous]

Hi Experts

I have table below, where GAP=ABS([FCST]-[ACTUAL]), where FCST and ACTUAL are all measures referring to the same table.

This works pretty fine at line level, but as you can see the result on total is wrong. PBI directly takes the sum of FCST and ACTUAL for calculation, rather than the sum of GAP, which is the desired result.

Thanks.

[Anonymous]

Thanks Jihwan it works. But may I know why? According to the definition of VALUES: "When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column", so VALUES ( 'Table'[Material] ) shall only return a single list of [Material], how can SUMX still perform the calculation over other columns?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not very good enough in DAX to explain well about the DAX formula, but let me try.

Please try the below two measures and there will be a difference between those two.

I think in general situation, dax formula works row by row

So, in this case, if you want to sum the column by using your calculation, one-column-table has to be virtually created.

Once it is done, still one more thing has to be solved. Please try to create the below two measures and look into it if there is any difference.

One will provide the result that you want, and the other will provide the correct number only at the total level.

The reason is, CALCULATE function helps to iterate one row by one row inside the measure.

This is just one way among many other correct ways, and I am not sure whether this answer is the best or not from the perspective of slow or fast. If you do not have a very big size of a dataset, it does not matter, but if you have a very gib size of a dataset, performance has to be considered.

Measure 1 = SUMX ( VALUES ( 'Table'[Material] ), CALCULATE ( ABS ( [FCST] - [ACTUAL] ) ) )

Measure 2 = SUMX ( VALUES ( 'Table'[Material] ), ABS ( [FCST] - [ACTUAL] ) ) 

[Anonymous]

Thanks Jihwan for your patient explanation.

I tried your two measures with or w/o "calcualte", the result looks the same...

Meanwhile when I introduce another "Customer" column left to Material I found the the problem shows up again: once multiple customers are selected the Total of GAP  returns just the gap between total ACTUAL and total FCST, guess I shall modify the content in VALUES, but have no idea how to do it.

[Jihwan_Kim]

Hi, @Anonymous 

If you add more contexts into your visualization, the measure has to be written again by considering all the contexts. I only saw one context which was "Material".

If you want to add more contexts, please share your information by sharing your sample pbix file's link here. Then I can try to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi Jihwan

The context is simple, just add one more col left to the Material. With your formula, now the result of [GAP] by Material is correct, but error shows up again if by Dealer level. Total below shall be 397920, the measures just return the gap between Totals.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

If you do not want to show your material name and dealer name, please let me know the pattern, at least.

How many different dealers are there? How many materials types are there?

Is it 6 different types of dealers with 6 different types of materials?

Or, is it 2 different types of dealers with 4 different types of material?

I just created my own sample again. It is 2 different types of dealers and 6 different types of materials.

In this case, the previous measure that was written by me still works. Because it is 6 different types of materials in 6 rows.

So, I created another sample. It is 2 different types of dealers and 4 different types of materials, showing 6 rows of information. Now, my previous measure does not work.

I am not sure about how your table looks like because that information is hidden by red color.

However, your previous sample was 4-row table, and this sample is 6-row table, so I assumed and created 2 types-dealers and 4ypes-materials in 6 rows. I still cannot know whether I assumed correctly or not.

If my assumption is correct, try to write your measure something like below.

GAP2 =

SUMX ( KEEPFILTERS( ALLSELECTED( 'Table2'[Material], Table2[Dealer] )), CALCULATE ( ABS ( [FCSTs2] - [ACTUALs2] ) ) )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Funk-E-Guy]

Hi @Jihwan_Kim, follow-up question to this old post; what if each of the data points we want to reference all live in separate tables?

Edit: I've created a separate post with more context; https://community.fabric.microsoft.com/t5/Desktop/Measure-works-at-line-level-but-returns-wrong-tota...

I want to calculate the same exact outcome as the OP of this post, but heres how my measures/columns are set up.

For example, for me:

• [FCST] is a measure that references the 'Forecasts' table
  • CALCULATE(SUM('Forecasts'[Quantity]))
• [Actuals] is a measure that references the 'Transactions' table
  • CALCULATE(SUM('Transactions'[Sales Quantity]) 
• instead of 'Table 2'[Material] it was 'Materials'[Material]
• instead of 'Table 2'[Dealer] it was 'Dealers'[Dealer]

[Anonymous]

Hi Jihwan

Really appreciate your multi-scenarioed solutions, your inference is exactly correct and the measures exactly works! Tons of help to me, not only for this case as I can adapt it to new scenarios onwards.

[Jihwan_Kim]

Hi, @Anonymous 

If there are no more columns on the left side of your picture, please try the below.

GAP =
SUMX ( VALUES ( 'Table'[Material] ), CALCULATE ( ABS ( [FCST] - [ACTUAL] ) ) )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/