0 votes
1 view
in BI by (10.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 (20.7k 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:

Related questions

0 votes
1 answer
asked Jul 13, 2019 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
asked Jan 21 in BI by Chris (10.1k points)
0 votes
1 answer
asked 4 days ago in BI by Chris (10.1k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...