topic Re: Rank by company in different Periods in DAX Commands and Tips

Rank by company in different Periods

AldoMF — Thu, 05 Nov 2020 15:49:47 GMT

Hi,

I have a set of data for some companies for 5 different periods. I'd like to know the rank of each company in each period. The data should look like this:

COMPANY	Period 1	Rank	Period 2	Rank	Period 3	Rank	Period 4	Rank	Period 5	Rank
A	9,467	1	9,819	1	7,930	1	8,125	1	11,599	1
B	4,978	2	5,420	2	4,323	2	4,247	2	5,440	2
C	4,164	3	4,409	3	4,086	3	2,824	3	3,905	3
D	1,797	4	2,031	4	1,842	4	1,721	4	1,780	4
E	505	5	479	5	501	5	332	6	411	5
F	342	7	351	7	279	7	481	5	384	6
G	238	9	238	9	194	10	186	10	297	7
H	343	6	347	8	339	6	223	8	297	8
I	163	10	202	10	236	8	329	7	236	9
J	335	8	395	6	212	9	200	9	225	10

I have a rank maeasure like this:

Rank = RANKX(

ALLNOBLANKROW('IDC Print Prelim'[IDC Company]),

CALCULATE(

SUM('IDC Print Prelim'[Units K]))

)

However, when I want to show the rank for company A in a card for period 5 I get that it's #2 qhen in reality is #1. The other issue I've found is that my rank formula takes into account ALL the data so if I'd like to show Company C in period 5 it should come as 3 but instead it comes as 4. I'd like to know how can I calculate the rank of the companies depending on the period and this to be dinamic because all the companies sell similar products that are categorizes and I'd like to drill down on the different categories to see their rank on those product categories.

Is this possible?

Thanks in advance

Re: Rank by company in different Periods

Anonymous — Thu, 05 Nov 2020 16:20:50 GMT

Your table has to be unpivoted first to the form: Company|Period|Value. This is the T table. Then, you can create a measure:

[Total Value] = SUM( T[Value] ) [Rank] = IF( ISINSCOPE( T[Company] ), RANKX( ALLSELECTED( T[Company] ), [Total Value],,DESC,Dense ) )

This is so-called 'top-level measure' and should never be used in any iterators only in visuals (to know why please read about ALLSELECTED on www.sqlbi.com).

Now, if you put your companies on rows and periods on columns and drop the [Rank] measure onto the canvas, you'll get exactly what you've shown above.

Re: Rank by company in different Periods

amitchandak — Thu, 05 Nov 2020 16:41:49 GMT

AldoMF , Try like

Rank = RANKX(
allselected('IDC Print Prelim'[IDC Company]),
CALCULATE(
SUM('IDC Print Prelim'[Units K]))
)

Rank = RANKX(
filter(allselected('IDC Print Prelim'[IDC Company],'IDC Print Prelim'[period]) [period] =max([period])),
CALCULATE(
SUM('IDC Print Prelim'[Units K]))
)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Re: Rank by company in different Periods

AldoMF — Thu, 05 Nov 2020 19:16:15 GMT

Thank you, the first option returns 1 always, no matter what company I show on the card

The second option marks me error in this part:
'IDC Print Prelim'[period]) [period] =max([period])),

Thank you again for your help

Re: Rank by company in different Periods

AldoMF — Thu, 05 Nov 2020 20:19:55 GMT

Thank you but I think that's not what I need. I need a measure that gives me a rank for the company in a certain period of time and that rank and company's information will be showed in a multi row card. If I use your method, the rank in teh card will always be 1 as I'm selecting one company

Re: Rank by company in different Periods

Anonymous — Fri, 06 Nov 2020 05:23:01 GMT

Hi AldoMF ,

Unpivot your table like shown below.

Post this you can create a Calculated Column

Rank = RANKX(FILTER('Table','Table'[Period] = EARLIER('Table'[Period])),'Table'[Value])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)