create a new calculated table having top funded categories each year

Question

A table contains 3 columns namely Year, Category and Amount in which the year and amount is a calculated column.

Table 1:

+------+----------+--------+
| Year | Category | amount |  
+------+----------+--------+
| 2016 | A        |     50 |  
| 2017 | B        |     85 |  
| 2015 | A        |     90 |  
| 2017 | C        |    135 |  
| 2016 | C        |     55 |  
| 2015 | B        |    180 |  
| 2017 | A        |     30 |  
| 2016 | B        |     45 |  
| 2015 | C        |     60 |  
+------+----------+--------+

now I need to create a new table with top 2 rows having highest amount in each year using DAX i.e an output like below given table

+------+----------+--------+
| Year | Category | amount |
+------+----------+--------+
| 2015 | B        |    180 |
|      | A        |     90 |
| 2016 | c        |     55 |
|      | A        |     50 |
| 2017 | C        |    135 |
|      | B        |     85 |
+------+----------+--------+

My DAX:

New Table = CALCULATE(TOPN(3, 'Table 1', 'Table 1'[Amount],DESC),ALLEXCEPT('Table 1','Table 1'[Year]))

but I'm getting error. So can anyone suggest me the correct DAX to proceed with?

Foxan Ng Foxan Ng · Accepted Answer · 2017-12-06T16:11:58

You can create a Rank column with the following DAX:

Rank = 
COUNTROWS(
    FILTER(
        'Table 1',
        'Table 1'[Year] = EARLIER('Table 1'[Year]) &&
        'Table 1'[Amount] > EARLIER('Table 1'[Amount])
    )
) + 1

Then you can filter the table where Rank is less than or equal to 2.

create a new calculated table having top funded categories each year

1 Answers