topic Re: Rolling average for only one group in DAX Commands and Tips

Rolling average for only one group

Jrose — Thu, 05 Aug 2021 18:52:12 GMT

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))

Then a measure that calculates a three year moving average

m3YrAvg =
CALCULATE(
AVERAGEX(

SUMMARIZE('Table_1','Table_1'[Year4],"Tot_AY_Ct", CALCULATE(COUNT('Table_1'[person_id])))
,[Tot_AY_Ct]),
FILTER(ALL('Table_1'[Year4]),
'Table_1'[Year4] <= MAX('Table_1'[Year4]) &&
'Table_1'[Year4] > (MAX('Table_1'[Year4])-3)
)
)

This works fine, but I would like it to work for only Group A. I can use the filter pane, but it seems cumbersome on some visuals and I would rather it be filtered at the calculation. I've tried adding it in the FILTER statement, but can't get it to work without error. Haven't used DAX much, so just can't seem to come up with the right placement or syntax I guess.

Any suggestion is appreciated.

Re: Rolling average for only one group

amitchandak — Fri, 06 Aug 2021 06:16:29 GMT

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])
) )

Re: Rolling average for only one group

Jrose — Fri, 06 Aug 2021 13:53:49 GMT

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.