POWER BI: Modeling

Statistical Functions

RANK functions

Returns the ranking order.

There are two types of rank functions.

• RANK.EQ(value,column,[order])
• RANKX(table,expresssion,[value],[order],[ties])

The paramters in square brackets are optional.

RANK

Use when you want to find the rank of a specific value in a particular column.

Example:

An order quantity for 174,728 units is received. Find the rank of this order in terms of quantity compare to all other order quantities.

Answer

Deinfe the following measures in Power BI:

                      rank_it = RANK.EQ(174728,TransactionData[Quantity])
                      RankQ = SUMX(FILTER(TransactionData,[rank_it]),TransactionData[Quantity])
	            

Power BI Demo:

The measure RankQ returns the quantity associated with the specific rank using a SUMX and FILTER function.

RANKX

Use to rank items in a table based on an expression.

Example:

Rank sales revenue by State from 1 to 50.

Answer

Define the following measures in Power BI:

                 Rank Sales_By_State = RANKX(ALL(states[state]),CALCULATE(SUM(TransactionData[Revenue])))
	            

Power BI Demo: