Count percentage of products from each material

[DimaMD]

Hello community

In previous post we had problem with calculating percentage of products from raw materials. @smpa01 helped us to solve this case.

Now we have another problem, which is as following:

Our table consists of ready products and materials. We figured out in previous case how to count percentage of products from materials. For example:

Product 1	5
Product 2	10
Product 3	15
Material	100

To count percentage we need to divide product with material, so we do this as Product1 (5)/material (100)=5%

Current prolem is that in some cases we have not one, but several rows with materials and we need to find out how many percents does product contain in every material.

Product 1	5
product 2	10
product 3	15
material 1	100
material 2	110

So as result we need to figure out what percentage of each product contains in each material.

Product 1 has 5% in material 1 and 4,5 percent in material 2 for example.

For that purpose i think we need to create some virtual table that will write down the results for each product in each material, but I dont know how to do so, please help me solve this case.

Example pbix

[DimaMD · Accepted solution]

Hello community!
Thanks for help @smpa01. We found solution to solve our task. 

After making some changes in calculations we have result that we needed. 

I managed to properly calculate quantity of materials per every specificationID and documentID 

base = 
var _amtTbl = CALCULATE(SUM('Table'[Amount]),'Table'[Type] = "Material",TREATAS(VALUES(Plan[ID_Specification]),'Table'[ID_Specification]),REMOVEFILTERS('Product'[ID_Product]))
var _coefficientPlan = SUM(Plan[Coefficient])
return _coefficientPlan*_amtTbl


New_base = 
IF(
    HASONEVALUE(Plan[ID_Specification]),
    [base],
    SUMX(VALUES(Plan[ID_Specification]),
    [base]))

Next we calculated proper percent of product that was in materials. 

%pct = 
var _numerator = [New_base] 
var _denominator = CALCULATE(SUM('Table'[Amount]),'Table'[Type] = "Material",TREATAS(VALUES(Plan[ID_Specification]),'Table'[ID_Specification]),REMOVEFILTERS('Product'[ID_Product]))
return 
divide(_numerator,_denominator)

Thank you so much for help!

[smpa01]

@DimaMD  I am not understanding the requirement

[DimaMD]

Hi, @smpa01 

Maybe i didnt describe our problem correctly but I will try in another example.

The default table looks as following

Product 1	15
Product 2	20
Product 3	25
Material 1	100
Material 2	200

We need to calculate percentage of products in each material, so result should be as following:

Material1	100	Product 1	15	15%
Material1	100	Product 2	20	20%
Material1	100	Product 3	25	25%
Material2	200	Product 1	15	7,5%
Material2	200	Product 2	20	10%
Material2	200	Product 3	25	12,5%

We have similar table in example file table "Plan" attached in previous post

[smpa01]

@DimaMD  sorry it is still ot clear to me. Can you please create a fresh pbix and clearly explain the ask.

[DimaMD]

Hello @smpa01 

We have changed our example file to be more understandable.

Table 1 (Plan) has ID_specification, ID_product and Coefficient

Table 2 (Table) has ID_product, ammount, type, ID_Specification.

Both tables are connected with ID_specification and both have ID_Product. The difference is that "Plan" table has coefficient in every ID_product.

The goal is to make a mesure that will multiply coefficient from "Plan" with ammount of "material" in "Table" so that we can see (write down in matrix table near every product) what amount of "readyproduct" we will have, depending on what "ID_Specification" we use.

I will try to explain this on screenshot

Step 1: We take ID of products and coefficient from table "Plan" so we can further mutiply coefficient with amount of material

Step 2: we take ID_specification from "Plan" table and connect it with "Table", where it is related with materials

Step 3: we need to create mesure that will help us to make matrix table with ID product, ID specification and amount of products, that will be calculated as "Coefficient of productID (related to specification)" * "amount of material (with same ID specification)"

Step 4 is our goal: We need to summarize amount of products with same ID that were calculated in step 3 and divide it with total material ammount, so we will see "total plan percentage"

I will attach both PBIX and Excel files examples so you can see all concept. I understand that my explanation is quite difficult but I dont know how to do it in other way.

EXAMPLE PBIX FILE

EXAMPLE EXCEL FILE

[smpa01]

@DimaMD  please refer to the attached pbix,

I looked into the spreadhseet and replicated the output, not that I completely understood it. Why are you showing the output only for ID_Product=51 and not for ID_Product=50, I still don't know.

However, this is the output which is identical to the excel

[DimaMD]

HI, @smpa01 

When we used your mesure in example file, it worked properly. But when we tried it in our orginal file it doesnt work

For that reason we added additional data in example files, and it also didnt make correct result. So the previous example worked ok, but when you add more data - it doesnt work. After adding more data connection between two tables changed to "many to many".

Additional question (after soving previous problem) is can we show results in one matrix table?

Example file with more data you can find below
EXAMPLE PBIX FILE

[smpa01]

@DimaMD  the basics of data- modelling advises never to use many-to-many relationship.

You need to use a bridging table and a star schema to build a proper data model first. 

[DimaMD]

@smpa01 ,
We made changes in our scheme and created bridging table (product) 

After making this we are trying to filter our table with DocumentID, but filter doesnt work, instead collums calculates summary of product quantity and material quantity from whole table (from every document ID). We need this filter to work, but dont know how to do this while using solutions we discussed above.

If we have only one DocumentID everything works just fine, but when we add several other documentID's and trying to show results of them seperately by filtering documentID, filter doesnt work.

I made some changes in mesure, you can see all data in example file below. 

Please help me to solve this task
Example file