cancel
Showing results for
Did you mean:
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.

Can anyone help with the Percentile Rank measure DAX?

Thanks!

1 ACCEPTED SOLUTION
Community Support

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]
)``````

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

2 REPLIES 2
Community Support

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]
)``````

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

Super User

Also, see this:

Excel to DAX Translation - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### 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 Design Challenge

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

#### Check it out!

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

Top Solution Authors
Top Kudoed Authors