Forum Discussion
Challenge when aggregating
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
Hi Anonymous ,
[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.
1 Reply
Hi Anonymous ,
[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.