PERCENTRANK on a matrix table

[jessewysong]

Hey folks,

Amatuer here... I exported some Matrix table data from Power BI and added columns in Excel that ranked vendors in rows based on columns such as Revenue, Volume, and Average Selling Price. In Excel, I made a "Revenue Grade" by using percent rank on the Revenue column. 

=PERCENTRANK.INC([Revenue],[@Revenue],3)

How can I duplictate my ranked columns in a Power BI matrix table so the data stays up to date? I looked at the QuickMeasure options and none looked appropriate. I thought about trying to add the calcualted columns in my query, but how would an array work if the query was going line by line (the SQL data table is ~40,000 order line detail data rows) and the data hadn't been summed and transformed into a table yet?

I really appreciate the help. Thanks all!

Jesse

[v-jiascu-msft]

Hi @jessewysong,

Please check out the demo here. Then you can apply it on the columns you want.

Measure =
VAR x =
    IF ( HASONEVALUE ( Table1[Unit Sales] ), MIN ( 'Table1'[Unit Sales] ), 100 )
VAR lowerNum =
    CALCULATE ( COUNT ( 'Table1'[Unit Sales] ), 'Table1'[Unit Sales] < x )
VAR upperNum =
    CALCULATE ( COUNT ( Table1[Unit Sales] ), 'Table1'[Unit Sales] > x )
RETURN
    DIVIDE ( lowerNum, lowerNum + upperNum, 0 )

Best Regards,

Dale

[jessewysong]

Good morning,

Thank you for the reply, and especially for including a sample file. I created the suggested code (included at the end of this post) but all my values are returning 100. I tried a few different data types, thinking it might be a mismatch but no luck. 

One thing I had a bit more luck with yesterday was RANKX, but it ranked everything 1...x instead of on a percentile scale like PERCENTRANK does in Excel. You can see those columns above including Revenue Rank, Unit Slaes Rank etc. 

Unit Sales Rank = RANKX( ALLSELECTED('Product'[PreferredVendorID]), CALCULATE(SUM(InvoiceDetail[QtyShipped])))

I will keep working on this today, 

thank you,

Jesse

Measure =
VAR x =
IF ( HASONEVALUE ( InvoiceDetail[QtyShipped]), MIN ( InvoiceDetail[QtyShipped] ), 100 )
VAR lowerNum =
CALCULATE ( COUNT ( InvoiceDetail[QtyShipped] ), InvoiceDetail[QtyShipped] < x )
VAR upperNum =
CALCULATE ( COUNT ( InvoiceDetail[QtyShipped] ), InvoiceDetail[QtyShipped] > x )
RETURN
DIVIDE ( lowerNum, lowerNum + upperNum, 0 )

[jessewysong]

One thought I had; there are VendorID rows where UnitSales are 0; perhaps that is screwing it up as that would throw off calculations made off lowernum?