Can I create a formula off of my partially summed horizontal dimension?

[#1]

I have a pivot table with one expression, two vertical dimensions and one horizontal dimension.  My horizontal dimension is partially summed to give me a total along the right side of my table.  I would like to create a formula off of this total, is it possible?  My numerator would be a value based on the column name and the denominator would be this partially summed total number.

For example (in the attachment):  I want to divide ABC/Total

Message was edited by: Kate Roddy Example attached

[swuehl]

You can use the TOTAL qualifier in your aggregation functions (with an optional field list) to calculate the partial sum on detail level.

The Aggregation Scope‌

Assuming your expression is something like

=Count(FIELD)

the denominator could look like

=Count(TOTAL<Supervisor, Name> FIELD)

[assuming Supervisor and Name are the actual field names of your vertical dims]

[#3]

Hello,

Try like below:

=sum(dim_name)/sum(total <Name> dim_name)

BR,

Kuba

[#4]

This is going to be a very basic question, but where do I put this TOTAL qualifier formula?  I tried to enter it as an expression, but it creates columns under my dimension instead of just 1 column to the right of the table.

[swuehl]

An expression will create a cell per combination of dimensions.

If you say, you want just 1 column to the right of your table, are you talking about the total column?

I think you already got your values partially summed there (across the horizontal dimension) is this not what you want?

I am a bit unsure about the exact context you are trying to calculate your numbers.

The Aggregation Scope

Could you maybe create & post a small sample QVW that demonstrates your setting and describe your expected result e.g. using an excel file?

[#6]

Thank you so much for responding so quickly.  I have added a sample QVW file and an example of what I'm trying to achieve in an excel file to the original post.  In the excel file, column K is what I'm after.

[swuehl]

Maybe like attached?

edit:

Basically changed the expression to

=If(SecondaryDimensionality()=0,

Num(Sum({<Carrier2 = {'National General Preferred','NGIC'}>}IncludeComp) /Sum( IncludeComp),'#.00%','.',','),

Sum(IncludeComp)

)

[#8]

This worked!  The only thing is that I no longer have my 'Total' column (the actual number of my denominator).  Is it possible to have both the total and the total%? 

[swuehl · Accepted solution]

The easiest solution could be to include both values in the same expression path:

=If(SecondaryDimensionality()=0,

Sum(IncludeComp) & ' (' &

Num(Sum({<Carrier2 = {'National General Preferred','NGIC'}>}IncludeComp) /Sum( IncludeComp),'#.00%','.',',')

& ')',

Sum(IncludeComp)

)

[#10]

So awesome, this will work!  Thank you very much for your time.

I have one more complication if you're up for it:  I want to add a formula that would only sum the data if the date is today.  I will then duplicate this table (and place it right below the first table) and make the date formula, sum the data for the week.  Any ideas on how to accomplish this?