Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
6 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      erhan_79
      Post Prodigy
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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