Difference in the output of SUMX and Calculate function

[PradipMCT]

Hello Every one,

Thanks in advance for all your help. 

I have written 2 DAX functions using SUMX and Calculate functions: 

They are as follows:

Calculate function
QTY 3 and Color Red By calculate function = CALCULATE([Profit],FILTER('Sales table','Sales table'[QTY]=3),FILTER('Product Table','Product Table'[Colour]="Red"))

RESULT for Category "Bike", Color, "Red" is 2910

RESULT for Category "Scooter, Color "Red" is 1422 which is correct

SUMX function:

Qty 3 color red using related = SUMX(
FILTER(
'Sales table','Sales table'[QTY]=3 && RELATED('Product Table'[Colour])="red"
),
[Profit]
)

Result for Category "Bike", Color, "Red" is 3168

RESULT for Category "Scooter, Color "Red" is 1422 which is correct

My file can be accessed from:

https://1drv.ms/u/s!ArDdiHvYfT0RgZZ_necMn4T7ZaTmDg

FILE

What is the difference between both the functions and y is there a difference in one of the output and not the other one?

[PradipMCT · Accepted solution]

Can any one please help?

[Anonymous · Accepted solution]

Hi @PradipMCT,

I think this issue is caused with related function.

RELATED Function (DAX)

The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship.

When the RELATED function performs a lookup, it examines all values in the specified table regardless of any filters that may have been applied.

The document mentioned 'related' function will ignore filter effect which already applied on current calculation.

Regards,

Xiaoxin Sheng

[PradipMCT · Accepted solution]

Can any one please help?