Percentage of subttotal of the expended dimension

[#1]

Dear Experts

I have a pivot table with many dimensions: supervisor , sales person, reason, invoice number, brand...

I want to calculate the percentage of current row from the expended dimension:

     If I collapse all I want to see the % of each supervisor from the total company.

     If I expend the supervisor and see each sales person then  I want to see the % of each sales person from the total of his supervisor(team)........

The below is giving the total according to the sales person regardless of the expended dimension:

sum (PriceBefDi* Quantity)

/

sum(TOTAL <SalesPerson >PriceBefDi*Quantity)

Anyone can gelp?

[HirisH_V7]

Hi,

Can you post your sample app!

[swuehl]

Try something like

=Sum(PriceBefDi* Quantity) /
Pick(Dimensionality(),
Sum(Total PriceBefDi* Quantity),
Sum(Total<supervisor> PriceBefDi* Quantity),

Sum(Total<[sales person]> PriceBefDi* Quantity),

Sum(Total<reason> PriceBefDi* Quantity)

)

[#4]

HI

Thank you for your help but it is not working.

Is there a function that returns the "current dimension expended"

[swuehl]

That's what the Dimensionality() function should return.

For pivoted dimensions to the top, you need to use SecondaryDimensionality()  though.

Just create an expression with these functions and see what they return. Then build a pick() conditional to get the right aggregation function.