Back

Explore Courses Blog Tutorials Interview Questions
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.)

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. 

Related questions

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

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...