Incorrect totals between different years

[GVallentgoed]

i am trying to multiply the (data[cost/lbs] from 3 years against only the (sum(data[2023 LBS]) but something is incorrect with the filters so I am getting incorrect result for 2024 and 2025. 

The Values should be:
2024: 0.09841927 x 148,457,498.59 = 14,611,078.63
2025: 0.09367710 x 148,457,498.59 = 13,907,067.94

Current forumla:

TotalCost = SUMX(

    VALUES(Data[Destination Name]),

    CALCULATE(

        DIVIDE(

            SUM(Data[Trip Cost]),

            SUM(Data[BOL LBS])

        ) * CALCULATE(SUM(Data[2023 LBS]),ALLSELECTED(Data[Latest Delivery].[Year]

    )

)))

[DanielW_ · Accepted solution]

Hi @GVallentgoed ,
With the following formula I am able to get to the desired result:

sumxtripcost = 
SUMX(
    VALUES(Data[Destination Name]),
    VAR LbsCost =
        DIVIDE(
            SUM(Data[Trip Cost]),
            SUM(Data[BOL LBS])
        ) 
    VAR TotalLbs =
        CALCULATE(
            SUM(Data[2023 LBS]),
            ALL(Data[Lates Delivery].[Year])
        )
    RETURN
        LbsCost * TotalLbs
)

The results for this are:

The difference between the 2024 and 2025 expected totals shared earlier seem to be caused by decimal differences. Did you calculate them using the 4 decimal cost/lbs? 

[DanielW_ · Accepted solution]

Hi @GVallentgoed ,
With the following formula I am able to get to the desired result:

sumxtripcost = 
SUMX(
    VALUES(Data[Destination Name]),
    VAR LbsCost =
        DIVIDE(
            SUM(Data[Trip Cost]),
            SUM(Data[BOL LBS])
        ) 
    VAR TotalLbs =
        CALCULATE(
            SUM(Data[2023 LBS]),
            ALL(Data[Lates Delivery].[Year])
        )
    RETURN
        LbsCost * TotalLbs
)

The results for this are:

The difference between the 2024 and 2025 expected totals shared earlier seem to be caused by decimal differences. Did you calculate them using the 4 decimal cost/lbs? 

[GVallentgoed · Accepted solution]

This works!  I had to make 1 adustment to your solution though because my master data set has 'Destination Names' that appear in some years and not others.  So, i changed the VALUES filter to "Lates Delivery [Year].  years with missing Destinations Names are not ignored. 

sumxtripcost =

SUMX(

    VALUES(Data[Latest Delivery].[Year]),

    VAR LbsCost =

        DIVIDE(

            SUM(Data[Trip Cost]),

            SUM(Data[BOL LBS])

        )

    VAR TotalLbs =

        CALCULATE(

            SUM(Data[2023 LBS]),

            ALL(Data[Latest Delivery].[Year])

        )

    RETURN

        LbsCost * TotalLbs

)

[v-sgandrathi]

Hi @GVallentgoed,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.

[v-sgandrathi]

Hi @GVallentgoed,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thank you.

[v-sgandrathi]

Hi @GVallentgoed,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

[rajendraongole1]

Hi @GVallentgoed  - i guess, issue is with the ALLSELECTED filter inside the CALCULATE function.

check the below modified calculation:

TotalCost =
VAR BaseLBS_2023 = CALCULATE( SUM(Data[2023 LBS]), ALL(Data[Year]) )

RETURN
SUMX(
VALUES(Data[Destination Name]),
DIVIDE( SUM(Data[Trip Cost]), SUM(Data[BOL LBS]) ) * BaseLBS_2023
)

Hope this works, please check

[GVallentgoed]

incorrect result 

[GVallentgoed]

Heres a small sample data set

Rate Year	Trip Cost	SCAC	TP Load Number	Lates Delivery	Destination Name	BOL LBS	BOL CF	2023 LBS	2024 LBS	2023 Rates	2024 Rates	2025 LBS	2025 Rates
2023	$   4,796.67	LLLE	850425645	2023-01-05 05:00	SOBEYS QUEBEC - TERREBONNE	11,268.69	1244.87	11,268.69		$   4,796.67
2023	$   1,308.08	TNXU	851274159	2023-01-27 06:00	CORE-MARK CALGARY	7,556.79	353.44	7,556.79		$   1,308.08
2023	$   3,472.17	TNXU	851526682	2023-01-30 12:00	COSTCO DEPOT 76	12,772.50	1013.33	12,772.50		$   3,472.17
2023	$   2,157.07	TNXU	851582572	2023-02-02 06:00	CORE-MARK CALGARY	11,191.01	479.14	11,191.01		$   2,157.07
2023	$   5,024.31	TNXL	851974808	2023-02-07 09:00	COSTCO DEPOT 76	16,150.00	1493.33	16,150.00		$   5,024.31
2023	$   5,835.07	PGPS	853552495	2023-02-24 18:00	SOBEYS QUEBEC - TERREBONNE	21,428.53	1043.21	21,428.53		$   5,835.07
2023	$   1,243.21	TNXU	873618756	2024-01-03 05:00	CORE-MARK CALGARY	6,356.44	275.48		6,356.44		$  1,243.21
2023	$   1,259.00	LLLE	873865681	2024-01-03 14:00	SOBEYS QUEBEC - TERREBONNE	18,408.65	1348.47		18,408.65		$  1,259.00
2024	$   2,387.46	TNXU	873874630	2024-01-08 08:00	COSTCO DEPOT 76	11,513.44	960		11,513.44		$  2,387.46
2024	$   2,853.41	TNXU	875038422	2024-01-29 05:00	CORE-MARK CALGARY	13,760.51	603.65		13,760.51		$  2,853.41
2024	$   2,157.67	TNXU	875598901	2024-02-02 06:00	CORE-MARK CALGARY	12,150.21	624.73		12,150.21		$  2,157.67
2024	$   3,564.64	TNXU	875613027	2024-02-05 12:42	COSTCO DEPOT 76	28,415.63	2346.67		28,415.63		$  3,564.64
2024	$   2,780.14	LLLE	876247038	2024-02-06 05:40	SOBEYS QUEBEC - TERREBONNE	21,515.89	1001.13		21,515.89		$  2,780.14
2025	$   1,259.00	LLLE	897358220	2025-01-02 10:50	SOBEYS QUEBEC - TERREBONNE	15,998.39	722.06					15,998.39	$ 1,259.00
2025	$   2,889.40	TNXU	897216331	2025-01-08 05:30	CORE-MARK CALGARY	13,934.06	686.61					13,934.06	$ 2,889.40
2025	$   2,478.82	TNXU	898527166	2025-01-31 08:00	COSTCO DEPOT 76	12,860.31	865.77					12,860.31	$ 2,478.82
2025	$   3,062.63	TNXU	898874898	2025-02-03 09:00	COSTCO DEPOT 76	15,889.13	1493.33					15,889.13	$ 3,062.63
2025	$   3,640.26	TNXU	899187722	2025-02-07 05:30	CORE-MARK CALGARY	17,555.04	808.02					17,555.04	$ 3,640.26
2025	$   1,204.89	LLLE	900442941	2025-02-21 10:50	SOBEYS QUEBEC - TERREBONNE	13,629.93	694.23					13,629.93	$ 1,204.89

[gmsamborn]

Hi @GVallentgoed 

Can you show your expected results based on the sample data you supplied?

[GVallentgoed]

Using the data sample provided this is the result vs expected result:
Multiply tripcost of each year by 2023 sum lbs

Expected Result: 
2023: $22,593.38
2024: $11,645.25
2025: $12,995.43

sumxtripcost = SUMX(

    VALUES(Sheet1[Destination Name]),

    CALCULATE(

        DIVIDE(

            SUM(Sheet1[Trip Cost]),

            SUM(Sheet1[BOL LBS])

        ) * CALCULATE(SUM(Sheet1[2023 LBS]),ALLSELECTED(Sheet1[Lates Delivery].[Year]

    )

)))

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[GVallentgoed]

unfortuantely, not possible as it is a work license bi.  Kindly copy paste the data sample provided into excel and create a sample bi on your end if possible 🙂

[gmsamborn]

Hi @GVallentgoed 

I built measures by steps since you look like you're displaying most of them. After looking at your measure and the solution provided, I had to take a step back and re-think since the numbers seemed way off. I ended up creating a measure for each step.

Trip Cost = SUM( 'Data'[Trip Cost] )

BOL LBS = SUM( 'Data'[BOL LBS] )

2023 LBS = 
    CALCULATE(
        SUM( Data[2023 LBS] ),
        ALL( 'Date' )
    )

Cost/LBS = DIVIDE( [Trip Cost], [BOL LBS] )

Expected Result = [Cost/LBS] * [2023 LBS]

The numbers are close so it might need a bit of tweaking.  (It seems that you have a couple of [Rate Year] values that don't match up with [Latest Delivery]. )

I hope I understood correctly.  Let me know if you have any questions.

Incorrect totals between different years.pbix

[GVallentgoed]

This solution results in blank values for 2024 and 2025 due to missing filter context.