Rank : adding product code dimension makes all ranks = 1

[AnneM]

Hi,

I made a table ranking the sum of sales revenue for each month per product name and it works well.

I was asked to add the product code dimension to the table, but when I do all my ranks turn to 1.

here is the formula I'm using :

=rank(sum({$ <Mois = {'janv.'}>}[Montant HT])) 

 here is what the table looks like once I've added the product code :

Thanks !

Edit : I've tried =rank(TOTAL sum({$ <Mois = {'janv.'}>} [Montant HT])) and it works but I need the ranks by "family products", where can I add this, something like <"family products"> ?

[Oleg_Troyansky · Accepted solution]

Hi AnneM,

The Rank() function ranks the most detailed (the last) dimension in the chart, within the context of the previous dimension - this is why all Ranks turned to 1 once you added another dimension.

You can do one of the following:

1. You can turn the Code Product (or the Product Description) into a Measure, like this:

     only([Code Product])

2. Or, you can add the keyword TOTAL to your Rank() function:

=rank(total sum({$ <Mois = {'janv.'}>}[Montant HT])) 

One of these two solutions should solve the problem.

Cheers,

[AnneM]

amazing thank you ! I used the first solution (the second one didn't correspond as I wanted to rank by products' family), and thank you for the explanation of why the ranks were equals to 1