Total row not showing the correct amount

[AquilaVictrix]

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.

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

[AquilaVictrix]

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

[Jihwan_Kim]

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.

[v-bmanikante]

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

[v-bmanikante]

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

[v-bmanikante]

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.

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

Regards,

B Manikanteswara Reddy

[AquilaVictrix · Accepted solution]

*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

        )

)

[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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Deku]

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 

[AquilaVictrix]

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