cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Super User II
Super User II

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




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

Proud to be a Super User!




View solution in original post

Highlighted
Super User II
Super User II

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

 





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

Proud to be a Super User!




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
Super User II
Super User II

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




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper V
Helper V

Re: Formula Help

@vimal_parmardear ;

 

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
Super User II
Super User II

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

 





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

Proud to be a Super User!




View solution in original post

Highlighted
Helper V
Helper V

Re: Formula Help

@vimal_parmar  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
Super User II
Super User II

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




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

Proud to be a Super User!




Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

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