Forum Discussion

RandyR1963's avatar
RandyR1963
Frequent Visitor
6 years ago

Calculate Ave Values in same Column by Date

Hi Experts,    Looking for help with a measure to calculate the difference in Column Values by Date,  I have created a Measure that gives me the average of the sum of a total clolumn by date ..  ...
Need Help
RandyR1963's avatar
RandyR1963
Frequent Visitor
6 years ago

kentyler , I am so sorry to cause so much confusion, in which i believe is on my part.. 

 

Lets see if i understood you correctly 

 

Created a Measure with 

CurrentWeek = VAR Current_Week = Max('Week/Week Data (2)'[Report Date])
RETURN
AVERAGEX(
Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
Current_Week <= Max('Week/Week Data (2)'[Report Date]) ),
[SumTotal61>180])  This does return the Current Week Value,  See Attached
 

I Also tried with Current_Week added as VAR this way .. 

PrevWeek =
VAR Prev_Week = DATEADD('Week/Week Data (2)'[Report Date], -7, DAY)
VAR Current_Week = Max('Week/Week Data (2)'[Report Date])
RETURN
AVERAGEX(
Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
Prev_Week <= Current_Week),
[SumTotal61>180])  Still get the error message as previously sent. 
 
 

 

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago
AVERAGEX(
Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
Prev_Week <= Current_Week),
[SumTotal61>180])  
So our next question is what's happening in the measure [SumTotal61>180], does it return a single value... or a set of rows ?
 
  • RandyR1963's avatar
    RandyR1963
    Frequent Visitor
    6 years ago

    kentyler 

     

    So our next question is what's happening in the measure [SumTotal61>180], does it return a single value... or a set of rows ? 

     

    SumTotal61>180 = Sum('Week/Week Data (2)'[Total 61>180])

     

    When i drop this into a Table, it returns one value, 

    when i add the Report date to the Table, it gives me the sum for each Report Date 

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago
    if you do
    AVERAGEX(
    Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
    'Week/Week Data (2)'[Report Date]<= Current_Week),
    [SumTotal61>180])  
    you should get the average up to the current week
    or 
    AVERAGEX(
    Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
    'Week/Week Data (2)'[Report Date]<= Prev_Week),
    you should get the average up to the previous week
     
    I admit, i'm getting a little lost in the problem. 
     
    [SumTotal61>180])  
    • RandyR1963's avatar
      RandyR1963
      Frequent Visitor
      6 years ago

      kentyler   .. I'm with you Sir.   I really appreciate your dilligence in assisting with this..  as we were working through this, i was able to find an alternative to the issue and get the results i was looking for. 

       

      WeekAVG =
      AVERAGEX(
      Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
      'Week/Week Data (2)'[Report Date] <= Max('Week/Week Data (2)'[Report Date]) ),
      'Week/Week Data (2)'[SumTotal61>180] )
       
      Last Week = 
      LW 61>180 = CALCULATE([SumTotal61>180], DATEADD( 'Week/Week Data (2)'[Report Date], -7,DAY)) 
      Drops the Week AVG down one week, i know this was what you were getting to
       
      Last Week Average
      LWWeekAVG =
      AVERAGEX(
      Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
      'Week/Week Data (2)'[Report Date] <= Max('Week/Week Data (2)'[Report Date]) ),
      'Week/Week Data (2)'[LW 61>180] )
       
      VAR 61>180=
      Var61>180 = [WeekAVG] - [LWWeekAVG]
       
      %VAR61>180=
      %VAR61>180 = [Var61>180] / [LWWeekAVG]
       
      Final Result is what i was looking for.