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

incorrect result 

 

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
  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    Hi GVallentgoed 

     

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

    • GVallentgoed's avatar
      GVallentgoed
      Helper II
      1 year ago

      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]
          )
      )))
      • gmsamborn's avatar
        gmsamborn
        Super User
        1 year ago

        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