topic Re: Total row not showing the correct amount in DAX Commands and Tips

Total row not showing the correct amount

AquilaVictrix — Wed, 19 Mar 2025 22:43:23 GMT

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.

Re: Total row not showing the correct amount

Deku — Wed, 19 Mar 2025 23:06:39 GMT

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

Re: Total row not showing the correct amount

lbendlin — Wed, 19 Mar 2025 23:41:18 GMT

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

Re: Total row not showing the correct amount

AquilaVictrix — Thu, 20 Mar 2025 02:46:26 GMT

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 truck

VAR 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

Re: Total row not showing the correct amount

Jihwan_Kim — Thu, 20 Mar 2025 03:48:59 GMT

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 ) )

Re: Total row not showing the correct amount

AquilaVictrix — Thu, 20 Mar 2025 14:28:33 GMT

Thanks for the response! Jihwan_Kim i used the provided DAX unfortunately, its showing still the wrong Sum at the total:

Re: Total row not showing the correct amount

AquilaVictrix — Thu, 20 Mar 2025 15:40:12 GMT

*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

        )

)

Re: Total row not showing the correct amount

Jihwan_Kim — Fri, 21 Mar 2025 03:53:48 GMT

Hi,

It is difficult to see what is the context of the filter on the page and on the visual.

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.

Re: Total row not showing the correct amount

Anonymous — Thu, 08 May 2025 12:31:09 GMT

Hi AquilaVictrix ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

Re: Total row not showing the correct amount

Anonymous — Mon, 12 May 2025 11:25:47 GMT

Hi AquilaVictrix ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

Re: Total row not showing the correct amount

Anonymous — Thu, 15 May 2025 13:20:04 GMT

Hi AquilaVictrix ,

May I ask if you have gotten this issue resolved?

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy