Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have recently been stumped trying to figure out how to create a % of a sub total.
For example I have a pivot table showing something like this:
County City Count of Sales
UK London 10
UK Liverpool 15
UK Manchester 75
France Paris 90
France Nice 10
So for the UK I would like to have another column saying 10% for London, 75% for Manchester etc.
I have been trying to create this in Set Analysis using the Aggr function, however it always uses the total of the entire pivot table, not the sub totals!
Any support would be massively appreciated!
Regards
Is this what you want?
Have you tried it with a pivot-table and enabled partial-sums? Further you might need a total statement within your expressions, like: count(TOTAL <City> Sales)
- Marcus
Yes I have, the number I want to get the percentage of is the partial sum total.
By doing what you have suggested I get 0% across the board...
I envisioned I would need to do something involving an Aggr function, before dividing that by the sales.
Hi,
Try this expression
count(TOTAL <Country> Sales)
Hi,
You can try, count(<City> Sales)/count(TOTAL <Country> Sales). This is if you have Country as a dimension. Than go to the properties->Number, for the expression, choose to show as a percentage
Hi,
Try using
=sum(SalesCount)/Aggr(
Sum ( TOTAL <County> [Count of Sales] )
,City,County)
Probably this is what I understand.
Is this what you want?
Have a look at this. May this help you.
Hi James,
This is exactly what I wanted, thanks a lot!
Jim
No worries, happy to help.