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.
I have a simple one table model. It contains invoices that each have a Tax ID, State, and Discount %. For example, in the screenshot below, TIN 99-9999999 in NH has 15 Invoices at 0% discount and 1 invoice at 0% discount.
I need a calculated column that ranks Discount % by invoice count within a given Tax ID/State combo. This column will be used for filtering.
I tried using SUMMARIZE to get counts for TIN/State/Discount, then RANKX on top of it. Screen shot below, table name redacted in blue.
In the output you can see the column (Rate Rank in orange) is doing some sort of state ranking but not what I'm looking for. My desired ranking is highlighted yellow.
Any advice would be greatly appreciated! I've tried many different arrangements of arguments but nothing is working. I've always been mystified by RANKX. Thanks.
Solved! Go to Solution.
Hi @cmarfisi,
From the screenshot, it is a matrix, and the [invoice count] is a column, you can create a calculated column using the following formula.
RANK = RANKX ( FILTER ( Table, Table[TIN] = EARLIER ( Table[TIN] ) && Table[State] = EARLIER ( Table[State] ) ), Table[invoice count], , ASC, DENSE )
If this doesn't resolve your issue, you can create dummy data similar with you for further analysis.
Thanks,
Angelia
Hi @cmarfisi,
From the screenshot, it is a matrix, and the [invoice count] is a column, you can create a calculated column using the following formula.
RANK = RANKX ( FILTER ( Table, Table[TIN] = EARLIER ( Table[TIN] ) && Table[State] = EARLIER ( Table[State] ) ), Table[invoice count], , ASC, DENSE )
If this doesn't resolve your issue, you can create dummy data similar with you for further analysis.
Thanks,
Angelia
Hi @cmarfisi,
Have you resolved your issue? If you have, please mark the right/helpful reply as answer. More people will new things here. If you haven't, please feel free to ask.
Best Regards,
Angelia
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |