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 ;   Ve...
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()))

     

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

       

      • erhan_79's avatar
        erhan_79
        Post Prodigy
        6 years ago

        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