Aggregation in Pivot Table

[LessaBern]

Hi, I haven't worked with QV in some time, so I'm a bit rusty and hope someone here can help me. 

See attached file: My company creates budgets on a project base, but the data needs to be displayed on a project and a subcompany level. 

At the moment, on the subcompany-level, the total budget-value is always displayed on every line, as per standard QV procedure:

But now I need to divide that value by the number of subcompanies per Budget_No, so that the values in the column will add up correctly:

If I could do this in the script with the fixed number of Subcompanies per Budget_No, I would have no problems - but they want a dynamic display, where the Budget is always divided by the available/ selected number of Subcompanies, e.g. if only these five are selected, the total value still needs to add up: 

I have tried everything I could find with aggr() and p(), but I can never get QV to display the actual values I need. Does anybody have a solution? It feels like it should be really simple, but apparantly, I'm too dumb.

[chrismarlow]

Hi,

Could you try something like;

if(Dimensionality()=1,sum(Budget), sum(Budget)/Count(TOTAL Project_No))

So;

Becomes;

Cheers,

Chris.

[LessaBern]

Hi Chris,

thanks so much for the reply, but the problem with "total" is that it gives me the total amount of subcompanies or projects across all dimensions, and divides every budget value by it - but I need the number of (selectable/ selected) subcompanies per Budget_No:

If not for that, your solution would have worked perfectly (or my solution with p() from earlier this day). 

Thanks!

[chrismarlow · Accepted solution]

Hi,

Maybe;

if(Dimensionality()=1,sum(Budget), sum(Budget)/Count(TOTAL <[Budget Number]> Project_No))

Cheers,

Chris.

[LessaBern]

Perfect, thanks! Now I feel stupid, I had completely forgotten how to aggregate a TOTAL by a dimension, this was the missing piece, thank you so much!