cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Super User I
Super User I

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

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

image.pngThis 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:

image.pngAgain, 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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Microsoft v-lionel-msft
Microsoft

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

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.

robertbu
Frequent Visitor

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

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

Super User I
Super User I

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

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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

robertbu
Frequent Visitor

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

Thanks djerro, that's the function that I wanted to look at, rank.

cheers,Robert

robertbu
Frequent Visitor

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

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. 
 
 
 
Super User I
Super User I

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

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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

robertbu
Frequent Visitor

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

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

Super User I
Super User I

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

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

image.pngThis 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:

image.pngAgain, 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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

robertbu
Frequent Visitor

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

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors