cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Formula Help

Hi there ;

 

İ need a your kind supports for a formula 

 

i would like to explain with below simple table ;

 

i have a column with many vendor names in a table as below , on this table ;

 

Vendor A = 2 qty

Vendor B = 7 qty

Vendor C = 13 qty

Vendor D = 11 qty

 

Capture.JPG

 

 

would like to create another table , which includes top 3 vendor according to line qty.

 

the new table will be as below : 

Capture-B.JPG

 

 thanks in advance for your kind help

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Formula Help

@erhan_79 Please create a table with as per below

Table = 
VAR _table = TOPN(3,SUMMARIZECOLUMNS(Test[Vendor Name],"CountOfVendor",COUNT(Test[Vendor Name])),[CountOfVendor],DESC)
VAR _top3 = SELECTCOLUMNS(_table,"Vendor Name",Test[Vendor Name],"Top number of vendor",RANKX(_table,[CountOfVendor]))
RETURN _top3

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Formula Help

@erhan_79 Please filter out the blank cells before counting. Replace 

COUNT(Test[Vendor Name]) with below formula

CALCULATE(COUNT(Test[Vendor Name]),FILTER(Test,Test[Vendor Name]<>BLANK()))

 

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Formula Help

@erhan_79 use RANKX function to achieve this, explained all this in post here..






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Anonymous
Not applicable

Re: Formula Help

@erhan_79 Please create a table with as per below

Table = 
VAR _table = TOPN(3,SUMMARIZECOLUMNS(Test[Vendor Name],"CountOfVendor",COUNT(Test[Vendor Name])),[CountOfVendor],DESC)
VAR _top3 = SELECTCOLUMNS(_table,"Vendor Name",Test[Vendor Name],"Top number of vendor",RANKX(_table,[CountOfVendor]))
RETURN _top3

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Formula Help

@Anonymousdear ;

 

thanks for your reply 

 

i need one more your help , when there are blanks cells , formula counts that blanks cells too, how can we stop to count blank cells for Vendor Name column

Highlighted
Anonymous
Not applicable

Re: Formula Help

@erhan_79 Please filter out the blank cells before counting. Replace 

COUNT(Test[Vendor Name]) with below formula

CALCULATE(COUNT(Test[Vendor Name]),FILTER(Test,Test[Vendor Name]<>BLANK()))

 

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Formula Help

@Anonymous  dear ;

 

may i ask one more help pls , this is last one 🙂 

 

i would like to not care the lines for this calculating which named material column includes "DMG" letters.While calculating ,  formula will not consider which material cell includes  "DMG" letters , this DMG code can be variable with numbers but "DMG"letters are fixed.

 

without "DMG" letters included lines will be calculated.

 

Could you help me about that pls 

 

Capture-d.JPG

 thanks in advance for your kind supports

Highlighted
Anonymous
Not applicable

Re: Formula Help

@erhan_79 

Table = 
VAR _table = TOPN(3,SUMMARIZECOLUMNS(Test[Vendor Name],"CountOfVendor",CALCULATE(COUNT(Test[Vendor Name]),FILTER(Test,Test[Vendor Name]<>BLANK()&&SEARCH("DMG",Test[Material],1,0)=0))),[CountOfVendor],DESC)
VAR _top3 = SELECTCOLUMNS(_table,"Vendor Name",Test[Vendor Name],"Top number of vendor",RANKX(_table,[CountOfVendor]))
RETURN _top3

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors