Forum Discussion
Powerbi Find most common value
Hey i have a issue with powerbi that i cant figure out.
The data looks like this
Suplier: | Item no: |
T00001 | STD0001
T00002 | STD0564
T00003 | STD06646
M00005 | STD054545
T00001 | STD0002
T00001 | STD0001
I need to be able to choose the suplier in this example lets say T00001 and get the result of its most common ITEM NO.In this case T00001 has two item numbers STD0001 and STD0002 but the most common in this case would be STD00001 and thats the result i need.I have a full list with suplier as a single value and i need next to them the most common Item no.Thanks for anyhelp
9 Replies
Hi Anonymous .
1)Create a measure using COUNTROWS as;
Supplier Count=COUNTROWS(Table).2)Create a measure for Common Supplier using RANKX as;
Common Supplier =FIRSTNONBLANK(TOPN(1,VALUES(Table'[Item no.]),RANKX(ALL('Table'[Item no.]),[Supplier Count],,ASC)),1)3)Drag a Card visual and add Coomon Supplier measure onto it.
4)Drag a slicer and add Supplier field into it.5)You will get the desired output.I tried it myself and I am attaching screenshot of the same.If this post answers your question, then please mark it as 'Accept as Solution' and give it a big thumbs up.
Regards,
Sanket Bhagwat.
Add a visual, consider for example a table.
Add the Supplier Column and Common Supplier measure onto it.
You will get the Common Item for the corresponding Supplier.
And the total 1000 rows doesn't mean that there are 1000 suppliers.
Suppliers are repeated.
You can get a count of total suppliers using DISTINCTCOUNT('Table'[Supplier]).Still if you want to view for only a limited number of rows, then you can filter the 'Supplier' column and include only the rows which you want to see.
Please let me know if you have any further doubts.
Regards,
Sanket Bhagwat