Row Number for Pivot Table

[microwin88x]

Hello,

I have a Pivot Table with the following data:

- Group/Customer are Dimensions

- Total is the Expression

Each Group can contain 1 or more Customers.

Group	Customer	Total
A1	AA	1000
A1	AB	1500
A1	AC	1700
A1	AD	1800
A1	AE	1900
A1	AF	2500
A1	AG	3500
A1	AH	3300
B1	BA	1000
B1	BB	800
B1	BC	500
B1	BD	300
B1	BE	550
C1	CA	3000
C1	CB	5000
C1	CD	1000
D1	DA	100
D1	DB	200
D1	DC	300
E1	EA	1000
F1	FA	1500
G1	GA	250
H1	HA	5000
H1	HB	7000
H1	HC	8000

What I need is to create a Ranking with numbers from 1 to N (sorted by Total expression):

Group	Total	Ranking
Total	52700
H1	20000	1
A1	17200	2
C1	9000	3
B1	3150	4
F1	1500	5
E1	1000	6
D1	600	7
G1	250	8

And then if I expand Group, I should see something like this (keeping the Ranking number for each Group):

Group	Customer	Total	Ranking
Total		52700
H1	Total	20000
	HC	8000	1
	HB	7000	1
	HA	5000	1
A1	Total	17200
	AG	3500	2
	AH	3300	2
	AF	2500	2
	AE	1900	2
	AD	1800	2
	AC	1700	2
	AB	1500	2
	AA	1000	2
C1	Total	9000
	CB	5000	3
	CA	3000	3
	CD	1000	3
B1	Total	3150
	BA	1000	4
	BB	800	4
	BE	550	4
	BC	500	4
	BD	300	4
F1	Total	1500
	FA	1500	5
E1	Total	1000
	EA	1000	6
D1	Total	600
	DC	300	7
	DB	200	7
	DA	100	7
G1	Total	250
	GA	250	8

Is there any way to do that on my chart?

Thank you!

[YoussefBelloum]

Hi,

Try this on the ranking expression:

=aggr(NODISTINCT rank(sum(total),Group),Group)

[devarasu07 · Accepted solution]

Hi,

may be this?

Rank:

Aggr( NODISTINCT Rank(Sum( Total)),Group)

Total:

sum(Total)

[microwin88x]

Thank you! It worked. The only thing is:

I have excluded Group = H1 like this: IF(Group<>'H1',Group) and then I've hidden Nulls.

When I have my Rank expression, it starts from 2, 3, 4, 5... (Because 1 would be H1)...

Is there any way to force it to start from 1?

Thanks again!

[microwin88x]

Thank you! It worked. The only thing is:

I have excluded Group = H1 like this: IF(Group<>'H1',Group) and then I've hidden Nulls.

When I have my Rank expression, it starts from 2, 3, 4, 5... (Because 1 would be H1)...

Is there any way to force it to start from 1?

Thanks again!

[antoniotiman]

In this case Expressions :

Sum({<Group-={'H1'}>} Value)

and

Aggr(NODISTINCT Rank(TOTAL Sum({<Group-={'H1'}>} Value)),Group)