Forum Discussion

phjz's avatar
phjz
Frequent Visitor
2 years ago
Solved

Changing Filter Context in Measure

Hey guys,

 

I have a measure that sums up values per week and multiplies them with a factor:

Actuals = Sumx(SUMMARIZE(Channels,Channels[Category]),sum('Actuals'[Value]) * (1 + min('Factors'[Value])))
 
Now, I want to calculate a YTD, which averages all Actuals until a selected calendar week. But, when I try this formula:
 
YTD = CALCULATE(AVERAGEX(SUMMARIZE('Actuals','Actuals'[CW],"Weekly",[Actuals]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))
 
...it doesn't work, because it seems, that DAX chooses the minimum of all factors for every single value before averaging. But I want it to apply a certain factor for every week and after that average the values.
 
Does anybody have an idea?
 
Thanks! 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi phjz ,

    Please have a try.

    YTD Actuals Average = 
VAR CurrentYear = MAX(Calendar[Year])
VAR LastDate = MAX(Calendar[Date])
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE('Calendar', Calendar[Year], Calendar[Date], "WeekActuals", [Actuals]),
        Calendar[Year] = CurrentYear && Calendar[Date] <= LastDate
    ),
    [WeekActuals]
)

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    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
    2 years ago

    this works perfectly - thank you so much!

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi phjz ,

    Please have a try.

    YTD Actuals Average = 
VAR CurrentYear = MAX(Calendar[Year])
VAR LastDate = MAX(Calendar[Date])
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE('Calendar', Calendar[Year], Calendar[Date], "WeekActuals", [Actuals]),
        Calendar[Year] = CurrentYear && Calendar[Date] <= LastDate
    ),
    [WeekActuals]
)

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    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

      this works perfectly - thank you so much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Actuals YTD = TotalYTD( [Actuals], Calendar[Date]
    Make sure your Calendar table is Marked as Date Table

    • phjz's avatar
      phjz
      Frequent Visitor
      2 years ago

      Thanks for your reply. It seems that TOTALYTD sums up the values, but I want them to calculate an average. Do you have an idea for this as well?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Then, you need a COUNT measure and then DIVIDE the 2 measures.