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
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 ?
 
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.