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

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

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.