Challenge when aggregating

[Le-analysis]

I am losing sleep over this one.. Any help is appreciated!
I need help with an expression in power BI DAX measure. I have two tables with the following relationship.
'Dim_Time'[Date] 1 -> * Fact_KSD_Logistics[Estimated time of departure]

In the table Fact_KSD_Logistics I have the following columns I want to use for an analysis

Values
1. 'Fact_KSD_Logistics'[Amount NOK Finance]
2. 'Fact_KSD_Logistics'[Total net weight]

Dimensions Rows:
1. 'Fact_KSD_Logistics'[From city]
2. 'Fact_KSD_Logistics'[To city]

Columns:
1. 'Dim_time'[Year]

I have the following expression. On the total for each row I get one value but on the details sum I get another.

LY CM Cost / weight = CALCULATE(sum(Fact_KSD_Logistics[Amount NOK Finance])/(sum(Fact_KSD_Logistics[Total net weight])/1000);SAMEPERIODLASTYEAR(Dim_Time[Date]))*(sumx(Fact_Distinct_KSD_Logistics;Fact_KSD_Logistics[Sum of Total net weight])/1000) 

From city	To city	Year	Actual cost	CM Actual Cost LY	CM Net Weight LY	Total net weight	CM Cost / MT LY	CM LY Price effect
STRAUMEN	BALTIMORE	2023	5 757 223	16 579 098	5 046	3 094	3 286	10 168 251
STRAUMEN	LYON	2023	5 829 634	10 832 249	31 049	31 013	349	10 819 679
Totals				27 411 347	36 095	34 107	759	25 901 236
Totals I want			27411347	36095	34106,5	759	20 987 930

The total I want should be 10168251 + 10819679 = 20987930
Instead I am getting 34107 * 759 = 25901236

[Anonymous · Accepted solution]

Hi @Le-analysis ,

[CM LY Price effect] is a measure, and then the formula is multiplied by [Total net weight] and [Total net weight], so your total is also two totals multiplied. If you want to solve this problem, you can create a new measure and then use the SUMX function.

Measure = SUMX('TableName',[CM LY Price effect])

Best Regards,

Stephen Tao

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