Forum Discussion

Jrose's avatar
Jrose
Frequent Visitor
5 years ago

Rolling average for only one group

Hi, I've looked through many similar posts and can't quite get to what I need. So far there is a calculated column to convert the year to a numeric year Year4 = value(right(Table_1[Year],4))   T...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Jrose , Try with help from two measures

 

Avg1= AVERAGEX( values('Table_1'[Year4]), CALCULATE(COUNT('Table_1'[person_id])))


m3YrAvg =calculate([Avg1],
FILTER(ALL('Table_1'[Year4]),
'Table_1'[Year4] <= MAX('Table_1'[Year4]) &&
'Table_1'[Year4] > (MAX('Table_1'[Year4])-3) && 'Table_1'[group] = MAX('Table_1'[group])
) )

Jrose's avatar
Jrose
Frequent Visitor
5 years ago

Thanks for the suggestion.  Unfortunately, it's not working.

The first calculation for average is giving numbers way too low and I can't tell what it is trying to average across. 

 

I tried the second calculation anyway, and it won't recognize the table columns.  The list that pops up as I enter the table name only shows the calculated fields and measures.  I typed it anyway and get an error that says "A single value for column "group" cannot be determined.  I used MAX to see if it would at least clear the error, which it didn't, but it doesn't make sense in this context anyway  I need to be able to say something like

where group = "A".

 

The original calculation I have works, I would just like to be able to add what is in the filter pane to the calculation instead as I anticipate needing it for other metrics in the future.

I guess I'm not understanding why it insists on an aggregate rather than being able to filter (select) only records of a certain value.  And why in this calculation it doesn't want to recognize regular data columns.