Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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 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
@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
@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 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
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
@erhan_79 use RANKX function to achieve this, explained all this in post here..
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |