Home > OLAP Calculation Templates > Share and Ranking > Rank
![]() Previous |
![]() Next |
Orders the members of a dimension based on the values of the selected measure.
You can choose a method for handling identical values:
Rank: Assigns the same minimum rank to identical values. For example, it may return 1, 2, 3, 3, 5 for a series of five dimension members.
Dense Rank: Assigns the same rank to identical values, so there may be fewer ranks than there are members. For example, it may return 1, 2, 3, 3, 4 for a series of five dimension members.
Average Rank: Assigns the same average rank to identical values. For example, it may return 1, 2, 3.5, 3.5, 5 for a series of five dimension members.
Rank members of the dimension dimension and hierarchy hierarchy based on measure measure (...). Calculate rank using { RANK | DENSE_RANK | AVERAGE_RANK } method with { member's level | member's parent | ancestor at level level } in order { highest to lowest | lowest to highest }. Rank NA (null) values { nulls last | nulls first }.
This calculated measure lists Products with the same parent in descending order based on the values of the Units measure.
Rank members of the PRODUCT dimension and PRODUCT.PRIMARY hierarchy based on measure UNITS (...). Calculate rank using RANK method with member's parent in order highest to lowest. Rank NA (null) values nulls last.
RANK() OVER HIERARCHY (PRODUCT.PRIMARY ORDER BY UNITS_CUBE.UNITS DESC NULLS LAST WITHIN PARENT)
The Rank column lists Hardware and Software products in descending order according to the number of Units sold.
Product Parent Units Rank --------------------- ------ ------- ---- CD/DVD HRD 442316 1 Modems/Fax HRD 395389 2 Desktop PCs HRD 291119 3 Memory HRD 138200 4 Monitors HRD 116963 5 Portable PCs HRD 87963 6 Accessories SFT 1793534 1 Operating Systems SFT 506853 2 Documentation SFT 228631 3 Software/Other TOTAL 2529018 1 Hardware TOTAL 1471950 2