How to apply row number based on certain fields in power pivot?

[JulieSamParticipant Started: 1Replies: 1Total: 2]

Hi All,

I have data like below

Country     State            Rank

India         Kerala            1

India         Kerala            2

India         Kerala            3

India         Tamil Nadu     1

India         Tamil Nadu     2

India         Orissa            1

India         Orissa            2

US            Florida            1

US            Florida             2

US            NewYork         1

I have to generate rank like this in power pivot. How can I achieve it?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello JulieSam,

Because you “have to generate rank like this in power pivot”, you would also need to use a column that has criteria (not currently shown) to rank the country-state combinations.

In order to demonstrate a way how to achieve this, I modified your data by making the rows more random, and adding a “criteria” column (the name of the column is not important, practically any column of numeric data would do):

Country State Criteria
India Kerala 1.1
US Florida 1.1
India Orissa 1.1
India Tamil Nadu 1.1
US NewYork 1.1
India Kerala 2.1
US Florida 2.1
India Orissa 2.1
India Tamil Nadu 2.1
India Kerala 3.1

After importing the data into Power Pivot (and naming the sheet-tab MyTable), I created a calculated column [Country State]:

= [Country] & ” – ” & [State]

Then I created another calculated column [Rank]:

=
CALCULATE (
COUNTROWS ( MyTable ),
FILTER (
ALL ( MyTable ),
MyTable[Criteria] <= EARLIER ( MyTable[Criteria] )
&& MyTable[Country State] = EARLIER ( MyTable[Country State] )
)
)

Returning to Excel from the Power Pivot window, I created a pivot and put Country, State and Rank columns on the Rows drop zone. The rest was just formatting field settings and pivot table options.

I also created a linkback table in Excel, using this formula:

EVALUATE
SUMMARIZE ( MyTable, MyTable[Country], MyTable[State], MyTable[Rank] )
ORDER BY MyTable[Country], MyTable[State], MyTable[Rank]

Attached is a copy of the workbook in 2013 format. The formula for the linkback table can also be found by right-clicking the table on the LinkBack tab, choosing the Table option, then Edit DAX.

If you have Excel 2010, let me know and I will also post a workbook in 2010 format (less the linkback table, which cannot be produced in Excel 2010).

If you have any questions, just reply to this email and I will answer them.

Attachments:

You must be logged in to view attached files.

[JulieSamParticipant Started: 1Replies: 1Total: 2]

Hi Tomallan,

Thanks for the response. I am new to power pivot. What is linked table? how can I add it? How does it work? can you please give a brief idea about it?

Regards,

julie

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Julie,

A linkback table is the result set in Excel from a query of Power Pivot tables.

Here is a link you can follow to learn more:

http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

When you read the linked reference on linkback tables, remember you can ask questions here on these forums and your thoughts will also help others learn.

[NICI2803Participant Started: 0Replies: 1Total: 1]

Hi,

I’m currently having a similar problem. Tried it with the formula shown above, but it is not fully what I need. I guess this formula is giving the rank by count, am I right? Actually I have a third column showing revenues and I would like to rank a combination of items by their revenue:

Region   Product    Revenue   Rank

Asia             A              500            2

Asia             B              1000          1

Europe        B              600            1

US                C                5000         1

US                A               1000          2

 

Is there any posibility to calculate it with a more or less easy DAX formula?

Thanks in advance,

Nici

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi,

Please comment if this is what you are looking for:

For each region, you want to rank products by revenue (The product with the highest revenue in each region will be ranked 1, the product with the next to the highest revenue in each region will be ranked 2…)?

If so, you could have a calculated column:

Rank as Calc Column = 1 + COUNTROWS (
FILTER (
‘Products By Region’,
[Region] = EARLIER ( [Region] )
&& [Revenue] > EARLIER ( [Revenue] )
)
)

or as a measure:

Rank As Measure:=IF (
HASONEVALUE ( ‘Products By Region'[Region] ) && HASONEVALUE ( ‘Products By Region'[Product] ),
1 + COUNTROWS (
FILTER (
ALL ( ‘Products By Region’ ),
[Region] = VALUES ( ‘Products By Region'[Region] )
&& [Revenue] > VALUES ( ‘Products By Region'[Revenue] )
)
)
)

If you have Excel 2013, please see attached workbook.

Tom

Attachments:

You must be logged in to view attached files.

[Author]

Posts