Forum Discussion

GVallentgoed's avatar
GVallentgoed
Icon for Helper II rankHelper II
1 year ago
Solved

Sumx wont calculate between different years

Avg Rate = Average Rate for all years ($5,831.25) 2024 total lbs = IF(Data[Latest Delivery].[Year]=2024,CALCULATE(SUM(Data[LBS])))  (747,832.10)   20204 Avg lbs = Average of 2024 lbs (23,369.75) ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi GVallentgoed ,

    Please try the following measure.

    2024sumx = 
CALCULATE(
    SUMX(Data,
        VAR AverageRateForEntireYear = CALCULATE(AVERAGE(Data[Trip Cost]), ALL())
        RETURN
        CALCULATE(Data[2024 Trucks] * AverageRateForEntireYear)
    )
)

     

    The final result is as follows. Hopefully it will meet your needs.

     

    Please see the attached pbix for reference.

     

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi GVallentgoed ,

Please try the following measure.

2024sumx = 
CALCULATE(
    SUMX(Data,
        VAR AverageRateForEntireYear = CALCULATE(AVERAGE(Data[Trip Cost]), ALL())
        RETURN
        CALCULATE(Data[2024 Trucks] * AverageRateForEntireYear)
    )
)

 

The final result is as follows. Hopefully it will meet your needs.

 

Please see the attached pbix for reference.

 

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • GVallentgoed's avatar
    GVallentgoed
    Icon for Helper II rankHelper II
    1 year ago

    THANK-YOU!  This was very close, I ony had to make 1 modifier so I could make multiple selections. 

    2024sumx = CALCULATE(
        SUMX(Data,
            VAR AverageRateForEntireYear = CALCULATE(AVERAGE(Data[Trip Cost]), ALL(Data[Trip Cost]))
            RETURN
            CALCULATE(Data[2024 Trucks] * AverageRateForEntireYear)
        )
    )