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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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