Apply dimension solely to one of many expressions.

[#1]

Hello, everyone. Hope you are doing good. I want to achieve the pivot table below.

FABRIC is a dimension, SUM(PRODUCTION) is the total production for that fabric, Right beside it, the total amount for each account.

It would be so lovely to be able to just drag the ACCOUNT dimension over SUM(AMOUNT) expression and achieve this, but, as you may have already guessed, I cannot. If I put the dimension in the upper section of my pivot table, it applies to EVERY expression (to SUM(PROD.) too!) and I don't want that: I want ACCOUNT dimension to apply solely to SUM(AMOUNT).

Can this be achieved?

Thank you very much!

Martín.

[Gysbert_Wassenaar]

Try enabling Show Partial Sums for the FABRICS and ACCOUNT dimensions and choose to show Subtotals on Top. You can find both options on the Presentation tab of the properties window of the pivot table.

[#3 · Accepted solution]

Hi Martin

How about creating Total Prod as calculated dimension with following definition:

=AGGR(SUM(PRODUCTION) , FABRICS)

Lukasz

[Anonymous]

Gysbert is absolutely correct, see attached qvw.

[#5]

It did not work,  Gysbert, but now that you mention it, I think I didn't make myself clear. I have corrected the picture. I don't want the total amount for each account; I want to be able to get SUM(AMOUNT) for each of the accounts, but if I try so, I end up dividing the other expression too (SUM(PRODUCTION)).

Thanks for your answer.

[#6]

Thanks, Bill, and thank you very much for your answer and example. However, the difference between your model and mine, is that, in yours, TOTAL PRODUCTION is the sum of every AMOUNT in the row, but in mine, AMOUNT and PRODUCTION have no relation whatsoever: TOTAL PRODUCTION is not the sum of the amounts.

Thanks again, anyway!

[Gysbert_Wassenaar]

Use sum(AMOUNT) as expression. Disable Show Partial Sums for all dimensions and add the calculated dimension lmastalerz described. And if you need more help post the document you're working on or a representative sample document.

[#8]

This is precisely what I was looking for! I did not know about AGGR usage. Thank you very very much, Lukasz!

[#9]

Totally with you, Gysbert, He was right, indeed. I really appreciate your guidance (it is not the first problem you help me with, and surely I hope it won't be the last!)

Thanks!