Rank categories by sum (Power BI)

Question

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 ♥

Answer 1

• From reference of documentation for the RANKX function, an optional ties argument which you can set to Skip or Dense. The default is Skip but you want Dense. Try this:

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

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

If you want to learn more about Power BI, then read the Power BI Tutorial. Also, enroll in Power BI Course to become proficient in this BI tool.