RANKX returning variable, TOP 1, 2, 3, 4

[rauerfc]

I have a table that has sales by month, account manager, and MRK.

I need that the measure returns the TOP 1 selling MRK and another one for 2, 3, and 4.

Output = 130 or 40 or 110

Each measure will be used in 4x charts, each one for the TOP 1,2,3,4, and this could vary by Account Manager.

Each Acc Manager could have differents TOP 1, 2, 3, 4 consequently, different charts.

Sample of dataset:

MRK	Manager	Month	Quantity Sales
10	921	Mar-21	9
10	952	Mar-21	2
40	921	Mar-21	22
40	952	Mar-21	53
40	4147	Mar-21	38
40	24829	Mar-21	6
110	4147	Mar-21	0
130	951	Mar-21	210
150	943	Mar-21	58
150	947	Mar-21	307
150	951	Mar-21	324
150	952	Mar-21	26
170	41687	Mar-21	129
170	921	Mar-21	2
170	922	Mar-21	12
170	923	Mar-21	201
170	924	Mar-21	85
170	943	Mar-21	8
170	947	Mar-21	28
170	951	Mar-21	221
170	952	Mar-21	26
170	3627	Mar-21	50
170	4147	Mar-21	52

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

Hi @rauerfc ,

Please try the following formula to create measures :

sum = CALCULATE(SUM('Table'[Quantity Sales]),ALLEXCEPT('Table','Table'[MRK]))

rank = RANKX(FILTER(ALL('Table'),'Table'[Manager]=MAX('Table'[Manager])),[sum],,DESC,Dense) 

If you want to separately get the rank value , you could use this:

Top 1 MRK = CALCULATE(MAX('Table'[MRK]),FILTER('Table',[rank]=1))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

What result are you expecting.  Explain the question clearly and show the expected result.

[rauerfc]

I need one measure that's going to calculate the TOP 1 selling MRK.

So if I select this Acc. Manager, the best-selling MRK will be 130.

And another measure to return the second best, third best, and fourth-best.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[rauerfc]

Thank you for your reply. 

I used your solution and it worked, is giving me the best selling, second best etc. 

And now my goal is to use this as a filter for the graph, for example:

So I want to filter information from the second-best for this graph:

I'm using your formula: 

mrk_cons_2 = CALCULATE(FIRSTNONBLANK(TOPN(1, VALUES(tab_mrk[MRK]),[sum_rank]),1), FILTER(VALUES(tab_mrk[MRK]),[rank_mrk]>1))

[Ashish_Mathur]

You are welcome.  So what problem are you facing?  Please be clear.

[rauerfc]

So, each account manager has a different best selling, second best, third best MRK. 

My visualization was built to show 4 graphs that will return the same information (Coverage), for the first, second, third, and fourth best-selling MRK. 

How can I show only information from the best selling in the first graph, 2, 3 and so?

 I'm trying to filter with the rankx selecting 1 and isn't working:

rank_mrk = RANKX(ALLSELECTED(tab_mrk[MRK]),[sum_rank],, DESC, Dense)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.