Rank by company in different Periods

[AldoMF]

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

[harshnathani]

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)

[amitchandak]

@AldoMF , Try like

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

or

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/3...

[AldoMF]

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

[Anonymous]

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.

[AldoMF]

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