RANKX / TOPN problem

[rwelsh11]

Having a really hard time getting a RANKX measure to work. My goal is to calculate the sum of the TopN (top 10) products for each year. I have a table of Products and their respective sales for 2017 and 2016:

PRODUCT ID |  YEAR  |  SOLD

-------------------------------

ProductID1   |   2017  |   $ 30,000

ProductID1   |   2016  |   $ 25,000

ProductID2   |   2017  |   $ 10,000

ProductID2   |   2016  |   $   5,000

ProductID3   |   2017  |   $   9,500

ProductID3   |   2016  |   $   4,000

...

I have created two measures to get the total sold for each year:

2017Sales = CALCULATE ( SUM ( 'Table1[SOLD] ) , 'Table1'[YEAR] = 2017 )

2016Sales = CALCULATE ( SUM ( 'Table1[SOLD] ) , 'Table1'[YEAR] = 2016 )

I have then created two ranking measures to rank each product within each year:

Rank2017 = RANKX ( ALL ( 'Table1' ) , 'Table1'[2017Sales] , , DESC , Skip )

Rank2016 = RANKX ( ALL ( 'Table1' ) , 'Table1'[2016Sales] , , DESC , Skip )

I have then created two more measures to calculate the sum of the Top 10 Products:

2017Top10Sum = CALCULATE ( 'Table1'[2017Sales] , TOPN ( 10 , ALL ( 'Table1[PRODUCTID] ) , 'Table1'[Rank2017] , ASC )

2016Top10Sum = CALCULATE ( 'Table1'[2016Sales] , TOPN ( 10 , ALL ( 'Table1[PRODUCTID] ) , 'Table1'[Rank2016] , ASC ) 

For some inexplicable reason, the Rank Measure is ranking some of the Product IDs the same when their sales are different (even stranger is that the RANKX seems to be assigning Products the same rank when their sales are close in value, but definitely not duplicate):

RANK | PRODUCTID | SALES 2017

------------------------------------

1        | ProductID1      | $ 30,000

2        | ProductID2      | $ 10,000

2        | ProductID3      | $   9,500

My sum function is also then returning bizarre answers. Using the table just above, if I asked for the sum of the Top 2 products in 2017, my Measure would return $49,500

If anyone could please help I would be so grateful! I have been trying to find a solution for days.

[Ashish_Mathur]

Hi,

If your objective is to determine the yearswise and productid wise revenue from top 10 products, then you do not need to create a Rank measure.  Try this

1. Create a matrix visual
2. Drag Year to the column headers section
3. Drag Product ID to the row headers section
4. Write the following measure

=SUMX(TOPN(10,VALUES(Data[Product ID]),SUM(Data[Sold])),SUM(Data[Sold]))

Hope this helps.

[rwelsh11]

Unfortunately, my goal is to create a 2 measures (one for each year) so that i can perform other functions with those totals (like calculate a variance). So i really need a measure that sums the top 10 Products in a given year. I actually have a table visual in my report that uses the TopN visual filter option to show the top 10 products for each year with a total at the bottom--my challenge is that i want to show the variance between those two table visuals and i can't seem to get my measures to match the sum at the bottom of my Top 10 table visual. 

[Ashish_Mathur]

Hi,

To compute the variance, you do not need two measures.  Just one should do.  Please share your expected result/layout so that i can compute the correct measure.