Forum Discussion
Total row not showing the correct amount
Hello i have this issue, i have a Measure called $ / Ton
$ / Ton = SUM(BD[MXN]) / SUM(BD[Movidas])
Also this measure called Impacto:
Impacto =
SUMX(
VALUES('BD'[Ruta]),
VAR VolumenAuto = CALCULATE(SUM('BD'[Movidas]), 'BD'[Transporte] = "Auto")
VAR VolumenFFCC = CALCULATE(SUM('BD'[Movidas]), 'BD'[Transporte] = "FFCC")
VAR CostoPorTonAuto = CALCULATE([$ / Ton], 'BD'[Transporte] = "Auto")
VAR CostoPorTonFFCC = CALCULATE([$ / Ton], 'BD'[Transporte] = "FFCC")
RETURN
IF(VolumenAuto > 0 && VolumenFFCC > 0,
ROUND((CostoPorTonAuto - CostoPorTonFFCC) * VolumenAuto, 0),
0
)
)
In the Rows the value of Imacto is correct and if i compare it with the excel data is all good but the problem is in the total is not showing the correct amount
In PBI
in Excel;
what am i doing wrong in the Impacto Measure? thanks in advance.
*update*
its Weird i have another table with Key Routes thata are conected to the Main BD
and with this the total is correct:Impacto =
SUMX(
VALUES(Rutas[Key]),
VAR VolumenAuto = CALCULATE(SUM(BD[Movidas]), BD[Transporte] = "Auto")
VAR VolumenFFCC = CALCULATE(SUM(BD[Movidas]), BD[Transporte] = "FFCC")
VAR CostoPorTonAuto = CALCULATE([$ / Ton], BD[Transporte] = "Auto")
VAR CostoPorTonFFCC = CALCULATE([$ / Ton], BD[Transporte] = "FFCC")
RETURN
IF(VolumenAuto > 0 && VolumenFFCC > 0,
ROUND((CostoPorTonAuto - CostoPorTonFFCC) * VolumenAuto, 0),
0
)
)
10 Replies
- Deku
Super User
I assume this is related to your Round(). In Power BI the total is not the sum of the rows in the table, it is calculated independly. Without more context it is hard to say
yes im trying to show the sum of the rows filtered (showed in the table), but the total is not right
i have this as result, the total in Movidas is on spot but the Impacto is not
im using cases in the variables:
in this Var i store the volume of ton moved by truckVAR VolumenAuto = CALCULATE(SUM('BD'[Movidas]), 'BD'[Transporte] = "Auto")
in this var the volume moved by Rail:VAR VolumenFFCC = CALCULATE(SUM('BD'[Movidas]), 'BD'[Transporte] = "FFCC")
And in these, the respective cost per ton moved:
VAR CostoPorTonAuto = CALCULATE([$ / Ton], 'BD'[Transporte] = "Auto")VAR CostoPorTonFFCC = CALCULATE([$ / Ton], 'BD'[Transporte] = "FFCC")
witch finaly calulates the Impact of using the Trucks in Rail Routes: only if the route has volume moved by truck and rail:IF(VolumenAuto > 0 && VolumenFFCC > 0,
ROUND((CostoPorTonAuto - CostoPorTonFFCC) * VolumenAuto, 0),
0
)Ruta Movidas $ / Ton Impacto 1 48 $390.20 $10,729 2 288 $260.10 $31,109 3 142 $145.60 $6,431 4 2208 $359.80 $255,937 5 153 $955.40 $90,485 6 144 $190.90 $13,085 7 502 $292.90 $57,928 8 35 $177.70 $1,713 9 72 $236.10 $1,473 10 48 $457.40 $8,929 11 49 $1,111.30 $39,255 12 284 $423.00 $42,207 13 586 $913.30 $377,486 14 246 $967.70 $55,280 15 48 $707.00 $5,602 16 145 $3,406.10 $232,141 17 48 $1,022.60 $2,498 18 35 $402.70 $7,146 19 70 $662.40 $6,449 20 49 $169.90 $2,996 Grand Total 5200 $13,252.10 $1,363,209
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Jihwan_Kim
Hi,
Please try something like below whether it works.
Impacto = SUMX ( VALUES ( 'BD'[Ruta] ), IF ( CALCULATE ( SUM ( 'BD'[Movidas] ), 'BD'[Transporte] = "Auto" ) > 0 && CALCULATE ( SUM ( 'BD'[Movidas] ), 'BD'[Transporte] = "FFCC" ) > 0, ROUND ( ( CALCULATE ( [$ / Ton], 'BD'[Transporte] = "Auto" ) - CALCULATE ( [$ / Ton], 'BD'[Transporte] = "FFCC" ) ) * CALCULATE ( SUM ( 'BD'[Movidas] ), 'BD'[Transporte] = "Auto" ), 0 ), 0 ) )Thanks for the response! Jihwan_Kim i used the provided DAX unfortunately, its showing still the wrong Sum at the total:
*update*
its Weird i have another table with Key Routes thata are conected to the Main BD
and with this the total is correct:Impacto =
SUMX(
VALUES(Rutas[Key]),
VAR VolumenAuto = CALCULATE(SUM(BD[Movidas]), BD[Transporte] = "Auto")
VAR VolumenFFCC = CALCULATE(SUM(BD[Movidas]), BD[Transporte] = "FFCC")
VAR CostoPorTonAuto = CALCULATE([$ / Ton], BD[Transporte] = "Auto")
VAR CostoPorTonFFCC = CALCULATE([$ / Ton], BD[Transporte] = "FFCC")
RETURN
IF(VolumenAuto > 0 && VolumenFFCC > 0,
ROUND((CostoPorTonAuto - CostoPorTonFFCC) * VolumenAuto, 0),
0
)
)
