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

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

  • GVallentgoed's avatar
    GVallentgoed
    Helper II
    1 year ago

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