Calculated dimension : count distinct locations per product where demand is >0 and location is a "plant"

[Sergej_Komar]

Hi community, 

I would appreciate your support on following: I need to build calculated dimension where it would represent a count of distinct locations per product where demand is >0 and location is a "plant", to further build a graph where I would see e.g, X products are produced on 1 plant, Y products are produced on 2 plants etc

There is a data set consisting of

LOAD
Material as %Material,
Sold_to_party as %Customer,
Location_code as %Location,
Year("Month")&'-'&num( Month("Month"), '00')&'__'&Material&'__'&Location_code&'__'&Sold_to_party as %Key1,
Year("Month")&'-'&num( Month("Month"), '00')&'__'&Location_code&'__'&Material as %Key2,
Year("Month")&'-'&num( Month("Month"), '00') as %YearMonth,
Demand_PCS as QTY,
GlobalSales_EUR as Sales_EUR

There is a separate table where there is match between Location (code) and location Type 

Thanks a lot 

[F_B]

Hi @Sergej_Komar ,

I would ideally try like this:

-create a temporary table that only includes locations that are classified as "plant" and have a demand greater than zero (using a where clause);

-create a summary table that counts the number of distinct plants per product;

-create your dimension that groups products by the number of plants where they are produced.