POwer BI Matrix visual and DAX formula to add new column

[Dimitri70]

Hello

I am trying to summurize in a matrix visual for each of suppliers how many part are shared among different plants and how many parts are not. 

I have this raw data: 

and below summary I would like to get in a matrix visual:

Thank you for your support

[Gabry]

What's you issue? You just need to make 3 measure with distinctcount like this:

Measure1= distinctcount(table(plant served))

Measure2= distinctcount(table(part shared))

then you put the supplier column and the measure in a matrix or table

[Dimitri70]

Hello Gabry,

If I do as you suggest, I will get the total count of Material by plant but I would like to have separetely the count of material that are shared  to different plant.

Exampe material ABC from supplier 100 is common to plant A and B so resutl is 2, while DEF material from supplier 200 is commom to plants A B and C so the total is 3

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

I modified the example data a bit.
Note that this would be easier if you have a dimension table for parts and plants instead of one table.

Dax:

Plant Served Count = DISTINCTCOUNT('Table (29)'[Plant Served])

Part Shared =

//If material is found is mutiple locations we count it as 1 else 0

SUMX(

SUMMARIZE(

ADDCOLUMNS('Table (29)',"Materialtest",

IF(

var _material = [Material Code]

 var _supplier = [Supplier]

 RETURN

CALCULATE(DISTINCTCOUNT('Table (29)'[Plant Served]),ALL('Table (29)'),'Table (29)'[Material Code]=_material,'Table (29)'[Supplier]=_supplier)>1,1,0)),

'Table (29)'[Material Code],[Materialtest]),

[Materialtest])

Part not shared = DISTINCTCOUNT('Table (29)'[Material Code])-[Part Shared]


End result:

 

Here material code jkl is not considered as a shared part since it is only used in one factory.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Dimitri70]

Thank you so much it works!