Need help with TopN and RankX for table that shows TopN parts shipped yesterday

[Roseventura]

My goal is to create a table that shows the top N part numbers shipped yesterday by Total Sales in descending order.

My table name is Invoices_2017.  I previously created 3 measures to show only Yesterday’s Sales, Qty Shipped and GP$ that I use for other purposes.

I followed the instructions in this document >> https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/ to create the additional table and measures.

I created a table called TopN which  has increments of 5, 10, 15, 20, 25, 30.  In that table I created a measure called SelectedTopNumber.

In addition to the 3 “Yesterday” measures mentioned above, I created these measures per the document:

• Total_Sales = calculate(sum(PBI_Invoice_17[Ext Sales Amount]),filter(PBI_Invoice_17,PBI_Invoice_17[Invoice Date]=PBI_Invoice_17[Yesterday]))

• Rank = Rankx(all(PBI_Invoice_17),PBI_Invoice_17[Total_Sales], ,DESC)

• Sales for TopN Parts = calculate(PBI_Invoice_17[Total_Sales], TOPN([SelectedTopNNumber], PBI_Invoice_17,PBI_Invoice_17[Total_Sales]))

The Values in my table visualization are:

 

Note:  I’m not using Sales for TopN Parts because when I do the totals are incorrect.

When I choose “5” from the filter, my table looks like this:

When instead, it should look this this:

As you can see, rank number is not sequential and there are 6 lines showing when there should be 5, thus throwing off the totals.

The same is true regardless of which number I choose from the filter.  If I choose 10, 12 lines show, etc. 

What am I doing wrong?

Thanks,

Rose

[Sean]

What column are you trying to Rank?

Try refencing the Column in the Rank Measure not just the Table

Rank = RANKX ( ALL ( PBI_Invoice_17[Column] ), [Total_Sales], , DESC)

Also does the Rank work if you use? without any filters

Total_Sales = SUM ( PBI_Invoice_17 [Ext Sales Amount] )

[Roseventura]

Sean,

The question I need to answer is this:  For instance, what were the top 15 parts we sold yesterday by dollar amount?  I'd like to give the user the ability to change the TopN number (5, 10, 15, 20, 25, 30) via the filter. 

We may have sold 500,000 of part ABC, but the totals Invoice may only be $10,000.  However, we may also have sold 1,000 pieces of part XYZ where the total invoice amount was $35,000.  So I'd need to see this:

Rank        Part         Amount     Qty

1              XYZ         $35,000     500,000

2              ABC        $10,000         1,000

I tried adding the Part Number to the Rank formula per your suggestion: 

       Rank = Rankx(all(PBI_Invoice_17[PART_NUMBER]),PBI_Invoice_17[Yesterday's Ranked Sales], ,DESC)

But now I get this:

I did try this without the filter and I get the same erroneous results.

Hope that helps.

Rose

[Roseventura · Accepted solution]

I figured it out!

Since I was trying to rank based on 3 different columns (Mfr, Part Number, Customer), I needed to include all 3 fields to the equation:

Rank = RANKX(all(PBI_Invoice_17[MANUFACTURER],PBI_Invoice_17[PART_NUMBER],PBI_Invoice_17[Customer Name]),[Previous Day Sales],,DESC)

It only took me 2 days looking through dozens of posts! 

Rose