Forum Discussion
SUM X
Hi,
Hopefully someone can help with a question that is driving me mad, I have a table containing details of vehicle trips - Customer a to Customer b - within this table I also have the mileage for each individual trip
I need to be able to calculate the overall mileage for each trip perfromed by specified date parameters, I have used the below measure
Hi Aaron197899 ,
A*C +B*D <> (A+B) * (C+D)
See the differences:
Measure = SUMX('Mileage & CO2',[Mileage]*[Total Jobs])Measure 2 = SUM('Mileage & CO2'[Mileage]) * SUM('Mileage & CO2'[Total Jobs])Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Aaron197899 , You should try multiplication at row level
Mileage By Customer = SUMX('Mileage & CO2','Mileage & CO2'[Mileage] * [Total Jobs])
if they are in different tables then use common dimensions or visual column
example
Mileage By Customer = SUMX(values(Dim[Column]), Sum('Mileage & CO2','Mileage & CO2'[Mileage]) * [Total Jobs])
Hi Amitchandak,
Thank you for your help,
Apologies - relatively new to BI
It's the grand total that isnt calculating correctly - the rows are fine but the total is mulitplying the "total jobs" by the summed mileage brief example below
Total JobsMileageMileage By Customer
9 126 1134 1 78 78 18 167 3006 53 21 1113 151 126 19026 16 78 1248 110 167 18370 19 9 171 6 63 378 30 33 990 44 13 572 27 95 2565 2 164 328 28 33 924 50 13 650 34 95 3230 5 107 535 603 1388 836964 The rows are fine it is the totals thats causing the issue ?
Any help greatly appreciated
Thanks
Aaron
Hi Aaron197899 ,
A*C +B*D <> (A+B) * (C+D)
See the differences:
Measure = SUMX('Mileage & CO2',[Mileage]*[Total Jobs])Measure 2 = SUM('Mileage & CO2'[Mileage]) * SUM('Mileage & CO2'[Total Jobs])Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
