0 votes
1 view
in BI by (17.6k points)

I need to rank products for my dashboard. Each day, we store the sales of products. In the result we have this dataset example:

+-----------+------------+-------+

| product   | date       | sales |

+-----------+------------+-------+

| coffee    | 11/03/2019 | 15    |

| coffee    | 12/03/2019 | 10    |

| coffee    | 13/03/2019 | 28    |

| coffee    | 14/03/2019 | 1     |

| tea       | 11/03/2019 | 5     |

| tea       | 12/03/2019 | 2     |

| tea       | 13/03/2019 | 6     |

| tea       | 14/03/2019 | 7     |

| Chocolate | 11/03/2019 | 30    |

| Chocolate | 11/03/2019 | 4     |

| Chocolate | 11/03/2019 | 15    |

| Chocolate | 11/03/2019 | 10    |

+-----------+------------+-------+

My attempt

I actually managed to Rank my products but not in the way I wanted it; In fact, the ranking process increase by the number of rows. for example, chocolate is first but we record 4 rows so coffee is ranked at 5 and not 2. 

+-----------+------------+-------+-----+------+

| product   | date       | sales | sum | rank |

+-----------+------------+-------+-----+------+

| coffee    | 11/03/2019 | 15    | 54  | 5    |

| coffee    | 12/03/2019 | 10    | 54  | 5    |

| coffee    | 13/03/2019 | 28    | 54  | 5    |

| coffee    | 14/03/2019 | 1     | 54  | 5    |

| tea       | 11/03/2019 | 5     | 20  | 9    |

| tea       | 12/03/2019 | 2     | 20  | 9    |

| tea       | 13/03/2019 | 6     | 20  | 9    |

| tea       | 14/03/2019 | 7     | 20  | 9    |

| Chocolate | 11/03/2019 | 30    | 59  | 1    |

| Chocolate | 11/03/2019 | 4     | 59  | 1    |

| Chocolate | 11/03/2019 | 15    | 59  | 1    |

| Chocolate | 11/03/2019 | 10    | 59  | 1    |

+-----------+------------+-------+-----+------+

sum field formula:

sum =

SUMX(

    FILTER(

         Table1;

         Table1[product] = EARLIER(Table1[product])

    );

    Table1[sales]

rank field formula : 

rank = RANKX(

    ALL(Table1);

    Table1[sum]

)

As you can see, we get the following ranking:

  • 1: Chocolate
  • 5: Coffee
  • 9: Tea

 

Improvements

I would like to transform the previous result into :

  • 1: Chocolate
  • 2: Coffee
  • 3: Tea

Can you help me improve my ranking system and get a marvelous 1, 2, 3 instead of this ugly and not practical 1, 5, 9?

If you don't know the answer, help by simply upvote the question ♥

1 Answer

0 votes
by (47.2k points)

rank = RANKX( ALL(Table1); Table1[sum]; ;; "Dense" )

  • (Those extra ; delimiters are there since we aren't specifying the optional value or order arguments.)

Related questions

0 votes
1 answer
asked Dec 15, 2020 in BI by Chris (10.1k points)
0 votes
1 answer
asked Dec 21, 2020 in BI by Chris (10.1k points)
0 votes
1 answer
asked Dec 1, 2020 in BI by Chris (10.1k points)
0 votes
1 answer
asked Dec 1, 2020 in BI by Chris (10.1k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...