Cell value as percentage of sub total

[#1]

Hi all,

I need to produce a pivot table where the cells in blue are expressed as a percentage of the total in the respective red cell within its ProductGroupName dimension.

Is this possible? The colour dimension is called colour,

Thanks!

[sunny_talwar]

You can use the concept of Dimensionality() here: How to use - Dimensionality()

Do you have an sample file where it can be implemented?

[abhaysingh]

Hi

Try This

sum(sales)/ sum(Total Sales)

thanks

Abhay

[#4]

My example is possibly a little more complex than standard. Each column for the colour dimension has its own expression, such as

=SUM({$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={1},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY)

[Anonymous]

try

sum(value)/sum(total <colour> value)

[#6]

I tried

=(SUM({$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={1},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY))/(

SUM(TOTAL{$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={1},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY))

Though this expressed it as a total of the two red cells combined if that makes sense

[sunny_talwar]

and total is yet another expression right??? What is the expression you are using for Total Column?

[jonathandienst]

Are Red, Green, Blue dimension values or expressions?

You will need something like

sum(sales)/ sum(Total <invYearValue, Colour> Sales)

-- if Colour is a dimension, or provide a sample of the document if you are using multiple expressions.

[#9]

=SUM({$<ProductGroupName={"Widgets","Stress Balls"},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY)

[sunny_talwar]

Maybe try this shot:

=(Sum({$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={1},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY))/

RangeSum(

Sum(TOTAL{$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={1},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY),

Sum(TOTAL{$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={2},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY),

Sum(TOTAL{$<ProductGroupName={"Widgets","Stress Balls"},ColourDWID={3},po_number -= {"*SAMPLE*"} > } ITEMQTY*ORDERQTY))

Assuming ColourDWID determine Red, Blue Colors here