Pro rata split of products applied to BOM weights

[Slipper]

Hello together,

I have a problem in applying a pro rata % split to weights of another table.

I have a BOM table of production of different material types from various input components.

I have a table with data from a production line which uses the material types, processing them and allocating them to product group and therefore to a sales stream/value stream.

As there is no physicial traceability, I want to use the % split of the destribution on product category based on the output weights to calculate how much of each input component per material type went into which product category & value stream.

The only relationships between both tables are the date/month and the material type, both filtered by a master table.

The desired outcome is then the distribution of the input component weights over the product group which I can summarize for the value streams and filter over time, material type, so it should be dynamic and I would like to use the summed tonnages for further calculations, like % of input compenent x over time in product group y etc.

In addition I have an external source "code 200" which should not be considered but that's the second priority.

I tried multiple things using ALL & ALLEXCEPT but I can't get it to work, as the filtering produces incorrect calculation while others doesn't work at all.

This is how I tried to do the % splits in the last try:

----

Percentage split =

Var one =

CALCULATE( SUM(Table1[Output weight] ), ALLEXCEPT( 'Table1' , Table1[Material type] ))

VAR two =

CALCULATE( SUM( Table1[Output weight] ), ALLEXCEPT( 'Table1' , Table1[Material type] , Table1[Product category] ))

Return

two / one

If someone can point me into the right direction or already knows how to accomplish would be great,

any help much appreciated, thanks in advance!

BR

Slipper

-----

Structure:

Tables:

Month	Input weight	Output weight	Source code	Material	Value stream	Product group
Jan-21	6000	5000	100	A	1	X
Jan-21	4500	4300	200	A	2	Y
Jan-21	4200	4100	100	B	1	G
Jan-21	6500	6400	200	B	2	H
Jan-21	8800	8700	200	B	2	J
Feb-21	12100	11000	200	C	2	t
Feb-21	13400	13300	200	A	2	f
Feb-21	17700	15600	100	B	1	n
Feb-21	18000	17900	300	A	2	o
Feb-21	21300	20200	200	C	3	t
Feb-21	22600	22500	100	A	2	p

Month	Material	Input component	Input weight
Jan-21	A	x	5000
Jan-21	A	y	10000
Jan-21	A	z	7500
Jan-21	A	t	8000
Jan-21	B	x	7600
Jan-21	B	y	4500
Jan-21	B	r	3700
Jan-21	B	v	870
Feb-21	A	y	4300
Feb-21	A	z	2000
Feb-21	A	t	6500
Feb-21	C	v	6700
Feb-21	C	y	1900
Feb-21	C	z	2300

[v-stephen-msft · Accepted solution]

Hi @Slipper ,

You can try this formula

Percentage split =
VAR one =
    CALCULATE (
        SUM ( Table1[Output weight] ),
        FILTER (
            ALLSELECTED ( 'Table1' ),
            [Material type] = MAX ( Table1[Material type] )
        )
    )
VAR two =
    CALCULATE (
        SUM ( Table1[Output weight] ),
        FILTER (
            ALLSELECTED ( 'Table1' ),
            [Material type] = MAX ( Table1[Material type] )
                && [Product category] = MAX ( Table1[Product category] )
        )
    )
RETURN
    two / one

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Slipper]

@v-stephen-msft 

Thanks! Worked as intended and I managed to modify it including the other splits I needed.

Best regrads,

Slipper