cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joyhackett
Frequent Visitor

Percentile Rank in Group

Hi,

 

I've been looking at the other posts for Percentiles, but I am not finding a similar scenario, so I'd appreciate any guidance.

 

I have a fact table with Category, Subcategory, Item, and Count. 

 

There is an Item dimension table but it is NOT RELATED to the fact table.

 

The user must select a Item from a slicer (using the Item dimension table) and a card will show it's subcategory percentile rank.

 

joyhackett_0-1628091126021.png

 

Can anyone help with the Percentile Rank measure DAX?

 

Thanks!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @joyhackett 

 

You can create two calculated columns in the table and then a measure to get the percentile value.

Two columns are:

Subcategory Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] )
    ),
    'Table'[Count],
    , // leave value argument blank
    DESC
)
PercentileRank = 
VAR _total =
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] ) )
    )
RETURN
    DIVIDE ( _total + 1 - 'Table'[Subcategory Rank], _total + 1 )

Measure:

Percentile = 
MAXX (
    FILTER ( ALL ( 'Table' ), 'Table'[Item] = SELECTEDVALUE ( 'Item List'[Item] ) ),
    'Table'[PercentileRank]
)

081001.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @joyhackett 

 

You can create two calculated columns in the table and then a measure to get the percentile value.

Two columns are:

Subcategory Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] )
    ),
    'Table'[Count],
    , // leave value argument blank
    DESC
)
PercentileRank = 
VAR _total =
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] ) )
    )
RETURN
    DIVIDE ( _total + 1 - 'Table'[Subcategory Rank], _total + 1 )

Measure:

Percentile = 
MAXX (
    FILTER ( ALL ( 'Table' ), 'Table'[Item] = SELECTEDVALUE ( 'Item List'[Item] ) ),
    'Table'[PercentileRank]
)

081001.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Greg_Deckler
Super User
Super User

@joyhackett See this: (1) Solved: PERCENTRANK (Inclusive) - Microsoft Power BI Community

Also, see this:

Excel to DAX Translation - Microsoft Power BI Community

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!