Powerbi Find most common value

[Anonymous]

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

[Anonymous]

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

[SanketBhagwat]

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

[Anonymous]

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.  

[SanketBhagwat]

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

[Anonymous]

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.

[Anonymous]

This is how the full query looks and i need to update it so that next to it we have most frequent values(Most common Item no for Source number).

[SanketBhagwat · Accepted solution]

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

)

[SanketBhagwat]

You cannot use DAX in Power Query Editor.

You have to first remove duplicates using Power Query Editor and then apply the measure in power BI Desktop.

[SanketBhagwat]

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.