Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need help with below requirement :
Pivot table - has two dimension category and customers. Need to derive at percentage of each customers spent on each category based on top 5 records. For eg :
Pivot Table-Top 5 | |||||
Category | Customers | Cust1 | Cust2 | Cust 3 | Cust 4 |
A | 10 | 30 | 23 | 34 | 78 |
B | 20 | 20 | 34 | 56 | 98 |
C | 30 | 10 | 56 | 45 | 45 |
D | 40 | 50 | 45 | 32 | 33 |
E | 50 | 40 | 68 | 12 | 22 |
Expected results : Get the % against each customers based on top 5 records. I have total of 283 records and i am showing only top 5 as of now. I am not able to get the Total no in the denominator to get the %. Please note these are count of transactions.
Script used - count(Total<Customer>,Category)
Pivot Table-Top 5 | ||||||||||
Category | Cust | % | Cust1 | Cust2 | Cust 3 | Cust 4 | ||||
A | 10 | 6.67% | 30 | 20.00% | 23 | 10% | 34 | 19% | 78 | 28% |
B | 20 | 13.33% | 20 | 13.33% | 34 | 15% | 56 | 31% | 98 | 36% |
C | 30 | 20.00% | 10 | 6.67% | 56 | 25% | 45 | 25% | 45 | 16% |
D | 40 | 26.67% | 50 | 33.33% | 45 | 20% | 32 | 18% | 33 | 12% |
E | 50 | 33.33% | 40 | 26.67% | 68 | 30% | 12 | 7% | 22 | 8% |
150 | 150 | 226 | 179 | 276 |
Any help will be appreciated.
Thanks,
Malaika
Have you tried with dimension limits? And mean time you can use rank
Dimension limits can be used only in the straight table and not pivot table