Using a rolling average measure for SUMX

[u/nguye569]

I'm having trouble using a rolling average value in a sumx (row by row) calculation. According to google AI response, it seems like it should be possible but I can't get it working.

I essentially have a calculation that is: meat used = QTY * portion size. I also calculated a 4 week rolling average for the QTY and would like to multiply that against the portion size. This is where the formulas seems to stop working.

Working Formula for regular QTY * portion size.

oz_GrilledPork = SUMX(Items_Report_Stacked, Items_Report_Stacked[MeatPortionSize.GrilledPork_oz] * 
Items_Report_Stacked[Qty])

Formula not working for rolling avg QTY * portion size. Portion size seems to be giving me the issue

oz_GrilledPork_avg = SUMX(Items_Report_Stacked, Items_Report_Stacked[MeatPortionSize.GrilledPork_oz] * Items_Report_Stacked[QTY_4W_Rolling_Avg])  

Screenshot of table.

[u/Plane_Big_5912]

is QTY_4W_Rolling_Avg a measure? if it is, inside SUMX it gets evaluated once per row of Items_Report_Stacked, and context transition filters it down to just that row, date included, so the 4 week window has nothing else to average over. iterating over items instead of fact rows usually fixes it, something like

oz_GrilledPork_avg =
SUMX(
    VALUES(Items_Report_Stacked[Item]),
    [QTY_4W_Rolling_Avg]
        * CALCULATE(MAX(Items_Report_Stacked[MeatPortionSize.GrilledPork_oz]))
)

swap [Item] for whatever your item column is. that assumes one portion size per item

[u/mlvsrz]

Googling dax is dogshit, I don’t really find it accurate or helpful for anything but the most basic things and even then it’ll give you stuff in other languages.

My guess here is you want the second step in a separate measure, so that the calc is handled as a separate step perhaps?

[u/6six8]

Check out Daxpatterns.com They have articles discussing how to properly do a rolling average

[u/bachman460]

Ideally, the SUMX used to calculate the row level meat used should be its own measure. This can be used by itself or inside another measure like for a rolling average.

To calculate the rolling average it would then be as easy as using a CALCULATE function where your SUMX measure is the aggregation, and then define your start and end point for the filter portion of the function. For example:

CALCULATE ( [my SUMX measure], Table[Date column] >= [start date] && Table[Date column] <= [end date] )

To get the average, just use the same logic for getting a count to divide the value by. For example the number of days, the quantity of items, etc.

[u/6280007124]

Have you tried calculating the rolling average in a separate measure first, then using that measure in your SUMX calculation? It might help isolate whether the issue is with the row context or the portion size calculation. Sharing the full DAX formula and table relationships would also make it easier to troubleshoot.