Forum Discussion

GVallentgoed's avatar
GVallentgoed
Helper II
1 year ago
Solved

Incorrect totals between different years

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]
    )
)))
Need Help
  • DanielW_'s avatar
    DanielW_
    1 year ago

    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's avatar
    GVallentgoed
    1 year ago

    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
    )

14 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
        GVallentgoed
        Helper II
        1 year ago

        Heres a small sample data set

        Rate Year Trip Cost SCACTP Load NumberLates DeliveryDestination NameBOL LBSBOL CF2023 LBS2024 LBS2023 Rates2024 Rates2025 LBS2025 Rates
        2023 $   4,796.67LLLE8504256452023-01-05 05:00SOBEYS QUEBEC - TERREBONNE  11,268.691244.87  11,268.69   $   4,796.67      
        2023 $   1,308.08TNXU8512741592023-01-27 06:00CORE-MARK CALGARY    7,556.79353.44    7,556.79   $   1,308.08      
        2023 $   3,472.17TNXU8515266822023-01-30 12:00COSTCO DEPOT 76  12,772.501013.33  12,772.50   $   3,472.17      
        2023 $   2,157.07TNXU8515825722023-02-02 06:00CORE-MARK CALGARY  11,191.01479.14  11,191.01   $   2,157.07      
        2023 $   5,024.31TNXL8519748082023-02-07 09:00COSTCO DEPOT 76  16,150.001493.33  16,150.00   $   5,024.31      
        2023 $   5,835.07PGPS8535524952023-02-24 18:00SOBEYS QUEBEC - TERREBONNE  21,428.531043.21  21,428.53   $   5,835.07      
        2023 $   1,243.21TNXU8736187562024-01-03 05:00CORE-MARK CALGARY    6,356.44275.48      6,356.44   $  1,243.21    
        2023 $   1,259.00LLLE8738656812024-01-03 14:00SOBEYS QUEBEC - TERREBONNE  18,408.651348.47    18,408.65   $  1,259.00    
        2024 $   2,387.46TNXU8738746302024-01-08 08:00COSTCO DEPOT 76  11,513.44960    11,513.44   $  2,387.46    
        2024 $   2,853.41TNXU8750384222024-01-29 05:00CORE-MARK CALGARY  13,760.51603.65    13,760.51   $  2,853.41    
        2024 $   2,157.67TNXU8755989012024-02-02 06:00CORE-MARK CALGARY  12,150.21624.73    12,150.21   $  2,157.67    
        2024 $   3,564.64TNXU8756130272024-02-05 12:42COSTCO DEPOT 76  28,415.632346.67    28,415.63   $  3,564.64    
        2024 $   2,780.14LLLE8762470382024-02-06 05:40SOBEYS QUEBEC - TERREBONNE  21,515.891001.13    21,515.89   $  2,780.14    
        2025 $   1,259.00LLLE8973582202025-01-02 10:50SOBEYS QUEBEC - TERREBONNE  15,998.39722.06          15,998.39 $ 1,259.00
        2025 $   2,889.40TNXU8972163312025-01-08 05:30CORE-MARK CALGARY  13,934.06686.61          13,934.06 $ 2,889.40
        2025 $   2,478.82TNXU8985271662025-01-31 08:00COSTCO DEPOT 76  12,860.31865.77          12,860.31 $ 2,478.82
        2025 $   3,062.63TNXU8988748982025-02-03 09:00COSTCO DEPOT 76  15,889.131493.33          15,889.13 $ 3,062.63
        2025 $   3,640.26TNXU8991877222025-02-07 05:30CORE-MARK CALGARY  17,555.04808.02          17,555.04 $ 3,640.26
        2025 $   1,204.89LLLE9004429412025-02-21 10:50SOBEYS QUEBEC - TERREBONNE  13,629.93694.23          13,629.93 $ 1,204.89
  • DanielW_'s avatar
    DanielW_
    Advocate I
    1 year ago

    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's avatar
      GVallentgoed
      Helper II
      1 year ago

      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's avatar
        v-sgandrathi
        Community Support
        1 year ago

        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.