Forum Discussion
Powerbi Find most common value
In that case create a new calculated column and paste that DAX formula into it.
This formula;
Common Supplier =FIRSTNONBLANK(TOPN(1,VALUES(Table'[Item no.]),RANKX(ALL('Table'[Item no.]),[Supplier Count],,ASC)),1)
hey the formula worked,but i was wondering how could i paste the result of the most common suplier into a query.Its query of around 1000 entries and only at like 700 do i need the most common suplier.And i cant find a way to pate it into a specific part of the column not the whole column
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
The second column has the item no the third column has the supliers i need so that the data filters doesnt show duplicate supliers and a 4th column appears with the most frequent item no.
In that case you can go to Power Query Editor and remove duplicates.
Home->Transform Data->Right Click on the Column->Remove Duplicates->File->Close and Apply.
Regards,
Sanket Bhagwat
Still how do i show the most frequent value in the data of Power Query cause i wanna use it for later so i somehow wanna have the most reacuring ITEM NO data in my queries.What function do i call in Power Quary Custom Table so i can get the same result that you did.