topic Why does a Pivot table automatically sum columns when using TOTAL? in App Development

Why does a Pivot table automatically sum columns when using TOTAL?

negovista — Sat, 16 Nov 2024 06:13:37 GMT

I am creating a pivot table with several dimensions. I would like to get the Total Market for Qty pr. month.

I use this formula to find the total

Sum ({<[Calendar.YearMonth]>} TOTAL <Market> qty)

It finds the total for Market as expected by summarizing the rows.

It does however summarize across column months as well. See screenshot below. How do I avoid this so that it shows the value for market per month for each row?

I thought {<[Calendar.YearMonth]>} would stop it from summarizing across months but it does not work.

Thx for any help.

Re: Why does a Pivot table automatically sum columns when using TOTAL?

negovista — Thu, 28 Mar 2019 15:49:03 GMT

I also tried with this formula

aggr(Sum ( qty)) , Market)

but it only shows one value unless I use nodistinct and with nodistinct it shows the summarized value for all months.

I also tried this

aggr(Sum ( qty) , Calendar.YearMonth)

and it summarizes per month, but it also summarizes all Markets.

It seems like I need a "for loop" saying for each month calculate total but that's not available with "set analysis" as far as I know.

Any other suggestions are appreciated