How to make a dimension subtotal (grouped total) use the same aggregation as the Grand Total in Qlik Cloud
I have a straight table with a measure that uses Avg() (e.g. Avg([Field])). I set that measure's "Totals" property to Avg, and the Grand Total row now calculates correctly (the true average of the visible rows).
However, I also have a subtotal row grouped by a dimension (something like Plant/Location) that still shows an incorrect value - as if it's using Sum instead of Avg, even though I can't find a separate aggregation property for that subtotal.
My question: in Qlik Cloud, how do I change the aggregation of the dimension subtotal (grouped totals) so it matches the Grand Total?
This isn't a hidden setting you're missing, it's just how straight tables behave. The Totals function you set on the measure (Auto/Sum/Avg/etc.) only controls the Grand Total row. Dimension subtotals ("Show subtotals" on a dimension) don't read that override at all. They just re-evaluate your base expression at that dimension's grain, and Qlik doesn't expose a separate aggregation dropdown for them because internally it's always treated as "Expression Total."
So when your Grand Total shows a correct Avg (because you told it to), the subtotal is technically still calculating Avg([Field]), just grouped by Plant/Location. If it looks like it's summing instead, the usual cause isn't the total setting, it's the grain of the data feeding that expression. If there's any fan out at a lower level than Plant/Location (a join bringing in extra rows, or the same [Field] value repeated across several detail rows in that group), Avg() will average over more rows than you expect and the result creeps toward something that looks like Sum.
Worth checking first: open a quick table with Plant/Location and Count(Field) next to Count(DISTINCT Field). If those numbers don't match, you've got duplication feeding the subtotal, and that's your actual problem, not the totals setting.
If the data is clean and it's still off, the reliable fix is to stop relying on the implicit per-group recalculation and force it explicitly with Aggr(), so the subtotal and Grand Total are guaranteed to use the same logic:
Avg(Aggr(Avg([Field]), [Plant/Location], [any other dimensions in the chart]))
List every dimension actually used in the table inside the Aggr, not just Plant/Location, or you'll get the same mismatch one level down. This pattern sidesteps the whole "what does the subtotal think it should do" question since you're explicitly telling Qlik the grain to average over before the outer Avg touches it.
One more thing to rule out: if [Field] is itself something already aggregated upstream (say, a value that's a sum per transaction, and you're averaging that), the subtotal can look sum-like just because there are very few distinct rows in some groups. Worth a sanity check with a temporary "Detail" dimension turned on to see what's actually feeding each Plant/Location bucket.
Let me know if the count check turns up duplicates, that's the most common root cause I've run into with this exact symptom.
In addition to the suggestions from @igoralcantara - in some scenarios it might be more suitable to use sum() / count() instead of avg(). Also be aware that any conditions within/wrapping the avg() may have a different impact to the rows as to the total-level.
