Forum Discussion

phjz's avatar
phjz
Frequent Visitor
2 years ago

YTD gets calculated in wrong order

I had a YTD calculation which worked well so far:

 

YTD = CALCULATE(AVERAGEX(SUMMARIZE('Data','Data'[CW],"Weekly",[Old_Value_Measure]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))
 
Now, I have to do some calculation with the Value-Measure for every week: New_Value_Measure = Old_Value_Measure * Total_Universe / Entitled_Universe. Both universe values have also numbers for every week with Total_Universe = CALCULATE(AVERAGE(
'Total Universe'[Wert]),FILTER(...)), Entitled_Universe works the same. The aggregation doesn't matter on a weekly base. So the table looks like this:
WeekOld_Value_MeasureTotal_UniverseEntitled_UniverseNew_Value_MeasureYTD_New
2024-0110,22126211956709222,7422,74
2024-0263,871251453554581144,1283,01

 

Now, the formula for YTD_New produces wrong numbers:
YTD = CALCULATE(AVERAGEX(SUMMARIZE('Data','Data'[CW],"Weekly",[YTD_New]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))

 

And I know why: The formula calculates the Average for Old_Value_Measure multiplies it with the Average for Total_Universe and divides it with the averarge for Entitled_Universe: 37,04 * 1256786 / 560837 = 83,01

 

But, what I want to do is to calculate the average of the calculated New_Value_Measures per week, so it has to be: 22,74  + 144,12 / 2 = 83,43

 

But unfortunately, I have absolutely no clue how to do this in Power BI. Does anyone have an idea?

 

Thanks a lot!

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi phjz 

     

    Please try the following DAX:

    MEASURE =
CALCULATE (
    SUM ( 'Table'[New_value] ) / COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date].[Year] = MAX ( 'Calendar'[Date].[Year] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

     

     

     

     

    Best Regards,

    Jayleny

     

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

     

    • phjz's avatar
      phjz
      Frequent Visitor
      2 years ago

      Thanks for your reply, but that doesn't work for me, because New_Value is a measure: New_Value_Measure = Old_Value_Measure * Total_Universe / Entitled_Universe

      So, I can't sum up this measure....

       

      Any other ideas?
      Thanks!