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

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        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.