Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I want to have a visual displaying some measures for my top 5 vendors by number of orders and another one by amount.
Question: what is the best practice to achieve this?
I was thinking one way would be to create another query, how is best to do that considering that I have a few dimension tables as well and I need some of the relations to be both way?
Is there a way to do it straight in the visual through a certain measure?
Cheers,
Robert
Solved! Go to Solution.
I think is what you are looking for (measure):
Ranking =
VAR summarizeTable = SUMMARIZE(ALL(PurchaseReceiptLines), PurchaseReceiptLines[cVendorName],"CountOrder", DISTINCTCOUNT(PurchaseReceiptLines[PRcpLine_Order_No]))
VAR rankedTable =ADDCOLUMNS(summarizeTable, "Rank", RANKX(summarizeTable, [CountOrder],,DESC,Dense))
VAR curVendor = SELECTEDVALUE(PurchaseReceiptLines[cVendorName])
RETURN
MAXX(FILTER(rankedTable, [cVendorName] = curVendor), [Rank])
First I create a summary table that depicts the following table (in memory, but I recreated for clarity):
Then I add a ranking column, based on the [CountOrder] column. This evaluates to this:
Then, I evaluate the only present Vendor in the current context. This measure is used in a table visual and is evaluated for a unique Vendor for every row. This variable returns that vendor name.
Lastly, I filter the in-memory ranked table based on the VendorName and take the MAX of the Rank column. This is because I know for sure I only have 1 row returned by the filter (every row had unique Vendorname). Then I add this measure to your table visual:
Let me know if this is the result you expected 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @robertbu ,
Maybe you can have a look at the answer:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your reply Lionel,
I cannot see anything regarding top 5 in that link, although I get that I could sort the resulting table based on my measure and show it in a summarize table.
But how would I be able to then retain only the first x records out of that sorting?
Cheers,
Robert
Proud to be a Super User!
Hi @JarroVGIT djerro,
Here's my situation, I have one table with some test data and some calculated measures/new columns as follows:
Below another view with the Order number as well, however the actual data has more detail line levels.
the ranking column shoud show 1 for the last line, 2 for Nod Publishers and remaining should have 3, 4, 5 (depending on the sorting) or even 3 on all of them. however it just shows 1 everywhere.
This is my formula, I tried a few options with no luck, it's clear to me I have to use Rank, but how:
Please share your test data, I will have a look. I can't make anything of your RANKX formula, it seems you are mishandling the creation of the calculatedtable and thereafter you are not referencing the table correctly. But, it is very hard to be precise but if you share your test data I can recreate your measure for you 🙂
PS> if you don't want to share here you can PM me
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi djerro,
Thanks so much! A simplified pbix can be downloaded here.
The Rankx function seems hard to crack at this stage for me as I'm fairly new to PowerBI, but with your help I might find out how to use it at least.
Kind Regards,
Robert
I think is what you are looking for (measure):
Ranking =
VAR summarizeTable = SUMMARIZE(ALL(PurchaseReceiptLines), PurchaseReceiptLines[cVendorName],"CountOrder", DISTINCTCOUNT(PurchaseReceiptLines[PRcpLine_Order_No]))
VAR rankedTable =ADDCOLUMNS(summarizeTable, "Rank", RANKX(summarizeTable, [CountOrder],,DESC,Dense))
VAR curVendor = SELECTEDVALUE(PurchaseReceiptLines[cVendorName])
RETURN
MAXX(FILTER(rankedTable, [cVendorName] = curVendor), [Rank])
First I create a summary table that depicts the following table (in memory, but I recreated for clarity):
Then I add a ranking column, based on the [CountOrder] column. This evaluates to this:
Then, I evaluate the only present Vendor in the current context. This measure is used in a table visual and is evaluated for a unique Vendor for every row. This variable returns that vendor name.
Lastly, I filter the in-memory ranked table based on the VendorName and take the MAX of the Rank column. This is because I know for sure I only have 1 row returned by the filter (every row had unique Vendorname). Then I add this measure to your table visual:
Let me know if this is the result you expected 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi Djerro,
Thank you, that's what I am looking for indeed.
I now understand the technique you used as well, thumbs up for explaining the steps!
Cheers,
Robert
Thanks djerro, that's the function that I wanted to look at, rank.
cheers,Robert
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |