Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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 )
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |