Total by Column instead of by Row

[nikonmike]

I am trying to create a table with percents related only to the specific line, like this:

If you like at each line (ie, Area 1) the percent is based on the allocation of amounts in the quarter.   Right now I am using the TOTAL function, but takes into account all Areas.

[sunny_talwar · Accepted solution]

Use TOTAL qualifier with the field name you want to TOTAL with respect to.... for instance, instead of using

Sum(TOTAL Measure)

use

Sum(TOTAL <Area> Measure)

[nikonmike]

That worked.  The Area dimension was a drill down, so I needed to add each level of the drill down to the <Area> portion of the formula and it worked. 

Thanks for the help. 

[JoSterling]

This works great with 1 qualifier.  Is there a limit to the number of qualifiers to be used with Total?

I have a pivot table with 5 dimensions, 1-2 as columns, 4-5 as rows.  I have tried placing all as rows or more in the columns and no matter what the calculations are incorrect at the lowest levels (or highest level of dimension) because the denominator will not drop to the row level. 

Ex:    Years, Month, Type, win/lose/open, name

Where I have a count of records at all levels divided by the total count of records at the level up.

(old post, but giving it a try for a response)

Thank you