cancel
Showing results 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 :

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

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 ;

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

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

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

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.