Rank in hierarchy

[bensca04]

Hi folks,

maybe someone can help with a rank-problem.

I have a table of customer-hierarchy, which I want to rank. This rank should get any customer of the hierarchy.

Example:

Hierarchy	Formula	Result
H1	rank(-sum(Sales),4)	2
H2	rank(-sum(Sales),4)	1
H3	rank(-sum(Sales),4)	3

But now I need the following table:

Hierarchy	Customer	Result
H1	A	2
H1	B	2
H1	C	2
H2	D	1
H2	E	1
H3	F	3

What is the formula?

I tryed (not succesfully): aggr(rank(-sum(Sales),4),Hierarchy)

Thanks for your ideas in advance!

[SBDataspark]

Hi there,

Would you be able to provide a small set of data to experiment with?

Kind regards,

Sebb

[AnalyticsBoost · Accepted solution]

Formula could something like this: Aggr(nodistinct Num(Rank(Total Sum(Sales),4)),Hierarchy)

[bensca04]

Thanks @AnalyticsBoost ; thats it. The "nondistinct" was the solution.