Rank in pivot table

[Sylvain1]

Hi Community,

I have the following table model (see attachement)

I need to create a pivot table with

- in lines the hierarchie country and department (with extend collapse)

- in columns the hierarchie segment and detail segment (with extend collapse)

(this part is easy 🙂 )

and for datas (with a mandatory filter on Brand) I need two informations:

  - calculate the rank based on the sum(sales) of the selected Brand compare to others brands

  - calculate the difference of sum(sales) between the next or previous rank of the selected Brand

I hope it's clear enough.

I think I have to use rank function with an agregation but how exactly?

I tried from a severals days to find a solution for my needs, but unfortunately I can't manage it for now, and did not find a solution in this forum.

thank you all,

Sylvain