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

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

the new table will be as below :

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

6 REPLIES 6
Highlighted
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``````
Highlighted
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()))``

Highlighted
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

thanks in advance for your kind supports

Highlighted
Anonymous
Not applicable

## Re: Formula Help

``````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?

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

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

#### 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