Forum Discussion

jessewysong's avatar
jessewysong
Helper III
8 years ago

PERCENTRANK on a matrix table

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....
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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's avatar
jessewysong
Helper III
8 years ago

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's avatar
    jessewysong
    Helper III
    8 years ago

    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?