topic Re: POwer BI Matrix visual and DAX formula to add new column in DAX Commands and Tips

POwer BI Matrix visual and DAX formula to add new column

Dimitri70 — Mon, 25 Mar 2024 08:04:24 GMT

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

Re: POwer BI Matrix visual and DAX formula to add new column

Gabry — Mon, 25 Mar 2024 08:11:01 GMT

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

Re: POwer BI Matrix visual and DAX formula to add new column

Dimitri70 — Mon, 25 Mar 2024 08:23:10 GMT

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

Re: POwer BI Matrix visual and DAX formula to add new column

ValtteriN — Mon, 25 Mar 2024 09:43:42 GMT

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/

Re: POwer BI Matrix visual and DAX formula to add new column

Dimitri70 — Mon, 25 Mar 2024 11:18:34 GMT

Thank you so much it works!