Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (11.1k points)
edited by

I want to rank my products based upon sales and my table looks like this:

+-----------+------------+-------+
| 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    |
+-----------+------------+-------+

I want to rank in such a way that 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    |
+-----------+------------+-------+-----+------+
For which I have created measure
sum =
SUMX(
    FILTER(
         Table1;
         Table1[product] = EARLIER(Table1[product])
    );
    Table1[sales]
)
rank = RANKX(
    ALL(Table1);
    Table1[sum]
)

How can I get the following result

1 Answer

0 votes
by (22.5k points)
edited by

You can use the following measure:

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

Looking forward to mastering Power BI? You can check out this Power BI Developer blog to get an in-depth understanding of the same. You can also check out the below video tutorial offered by Intellipaat to know more about it:

You can register for this online Power BI training course by Intellipaat to learn Power BI!

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.6k answers

500 comments

108k users

Browse Categories

...