Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robertbu
Frequent Visitor

Best technique to create visual for top 5 vendors by a certain measure

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

1 ACCEPTED 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):

This is the result of the first VAR, but in memory so you won't see it.This is the result of the first VAR, but in memory so you won't see it.

Then I add a ranking column, based on the [CountOrder] column. This evaluates to this:

Again, this evaluates in memory.Again, this evaluates in memory.

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:

image.png

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
v-lionel-msft
Community Support
Community Support

Hi @robertbu ,

Maybe you can have a look at the answer:

Sorting by multiple columns 

 

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

Without any datamodel, the best I can recommend is to look at this article:
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
You'll need to create a ranking and then apply a visual filter (where rank is lower than 6). You can also filter TopN in visual filters but is less advanced.




Did I answer your question? Mark my post as a solution!

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:

 

2020-01-06 10_21_13-BC Inventory DIFOT Receipt Driven - Power BI Desktop.png

 

Below another view with the Order number as well, however the actual data has more detail line levels.

2020-01-06 12_12_24-BC Inventory DIFOT Receipt Driven - Power BI Desktop.png

 

 

 

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:

mNoOfOrdersRanking =
var vendorNo = SelectedValue('ACPBI_PurchaseRcptLines'[BuyFromVendorNo])
RETURN
RANKX(
CALCULATETABLE(
SUMMARIZE(ALLSELECTED('ACPBI_PurchaseRcptLines'),'ACPBI_PurchaseRcptLines'[cVendorName],'ACPBI_PurchaseRcptLines'[PRcpLine_Order_No]),
ALLSELECTED()
),
CALCULATE( DISTINCTCOUNT('ACPBI_PurchaseRcptLines'[PRcpLine_Order_No])),
//FILTER(ALLSELECTED('ACPBI_PurchaseRcptLines'),'ACPBI_PurchaseRcptLines'[BuyFromVendorNo] = vendorNo)),
, DESC,Dense)
 
Among other trials I also tried this:
mNoOfOrdersRanking =
var vendorNo = SelectedValue('ACPBI_PurchaseRcptLines'[BuyFromVendorNo])
RETURN
RANKX(ALLSELECTED('ACPBI_PurchaseRcptLines'),
CALCULATE( DISTINCTCOUNT('ACPBI_PurchaseRcptLines'[PRcpLine_Order_No]),
FILTER(ALLSELECTED('ACPBI_PurchaseRcptLines'),'ACPBI_PurchaseRcptLines'[BuyFromVendorNo] = vendorNo)),
, DESC,Dense)
 
IF I remove the filter part from the Calculate function and sort Ascending weirdly enough I get a different result, with the last two vendors (Nod and WWImporters) showing a ranking of 2, while the rest show 1. 
 
 
 

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! 🙂





Did I answer your question? Mark my post as a solution!

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):

This is the result of the first VAR, but in memory so you won't see it.This is the result of the first VAR, but in memory so you won't see it.

Then I add a ranking column, based on the [CountOrder] column. This evaluates to this:

Again, this evaluates in memory.Again, this evaluates in memory.

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:

image.png

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! 🙂





Did I answer your question? Mark my post as a solution!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.