Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |