Find rank or percentile for a group

[Anonymous]

Hello All , I am a beginner in Power BI. I am trying to build a dashboard with following data - 

Data - 

Product_ID	Competitor	Price	Date
ABC1	F	100	Jan-19
ABC1	XYZ	60	Jan-19
ABC1	A	10	Jan-19
ABC1	M	40	Jan-19
ABC1	B	41	Jan-19
PQR3	F	16	Mar-19
PQR3	XYZ	75	Mar-19
PQR3	A	156	Mar-19
PQR3	M	23	Mar-19
PQR3	B	121	Mar-19
ABC1	F	102	Mar-19
ABC1	XYZ	98	Mar-19
ABC1	A	109	Mar-19
ABC1	M	46	Mar-19
ABC1	B	12	Mar-19
ABC1	R	19	Mar-19
ABC1	D	156	Mar-19

Problem-
I want to find percentile rank for the competitior XYZ for each Product_ID based on price with date as a filter. Desired output should look like below - 

Date Filter : Mar-19

DESIRED OUTPUT : 

Product_ID	Percentile
ABC1	0.57
PQR3	0.6

Notes - 

0.57 because XYZ is placed at 4th out of 7 competitors. So 4/7=0.57

0.6 because XYZ is placed at 3rd out of 5 competitors. So 3/5=0.6

Could someone help me ? 

[joyhackett]

I am having a very similiar issue but my original table is not summarized by ProductID (as in the example in the OP).  

Is there a way to summarize the table as a calculated variable table first and then apply your logic?  My problem is that I cannot create a Calculated Column with the RankX - it has to be a measure as it is ranking on the SUM of Price.

@v-alq-msft

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

Then you may create a calculated column and a measure as follows.

calculated column
ColumnRank = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Product_ID] = EARLIER('Table'[Product_ID])&&
        'Table'[Date] = EARLIER('Table'[Date])
    ),
    'Table'[Price],
    ,DESC
)

measure:
Percentile = 
var _currentid = MAX('Table'[Product_ID])
return
DIVIDE(
    CALCULATE(
        SUM('Table'[ColumnRank]),
        FILTER(
            'Table',
            'Table'[Product_ID] = _currentid&&
            'Table'[Competitor] = "XYZ"
        )
    ),
    CALCULATE(
        MAX('Table'[ColumnRank]),
        FILTER(
            'Table',
            'Table'[Product_ID] = _currentid
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-alq-msft  , this worked like a charm. Could you explain the methodology you used ?