Formula Help

[erhan_79]

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

[Anonymous · Accepted solution]

@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 · Accepted solution]

@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

[erhan_79]

@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

[Anonymous · Accepted solution]

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

[erhan_79]

@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

[Anonymous]

@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

[parry2k]

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