Wrong Total in Matrix by using SMX-measure

[Anonymous]

Hello,

I got the following issue. I have to show a table with the values per year and I also have to show the sum of them. 

In order to consider a currency translation I am using the following measure to show the data: 

PS IRQ (in target CUR) = SUMX('Product Information','Product Information'[Currency Rate]*sum('Product Information'[Project Specific Cost (in EUR)]))

As you can see in the picture my values per year are correct. The issue is that the sum of them is not correct. I do not know how to solve this specific problem. Hopefully some of you got an idea how I have to change my measure so that all values will be correct.

I would really appreciate your help....!!!!

[v-luwang-msft]

Hi @Anonymous ，

Since there is no specific pbix file for you, you can refer to my similar template data below.

It showed me  2+2=3, on total,but I want to show here 4.

base data:

measure:

visit(cal) = DISTINCTCOUNT(Spending[date])

Visit = VAR _table = SUMMARIZE(Spending, Spending[member_number], "distinctcount", DISTINCTCOUNT(Spending[date]))
RETURN
SUMX(_table, [visit(cal)])

Final I create another  measure:

test = if(HASONEVALUE(Spending[branch_name]),[visit(cal)],SUMX(DISTINCT(Spending[branch_name]),[visit(cal)]))

Output:

You could adjust on my sample .And if question still not solved ,could you pls share your pbix file ,and remember to remove confidential data.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[davehus · Accepted solution]

Hi @Anonymous, Try this.

PS IRQ (in target CUR) = SUMX('Product Information','Product Information'[Currency Rate]*'Product Information'[Project Specific Cost (in EUR)])

Sum is an aggregation, where are SUMX is iterating through each line of the dataset.