Partially syndicated - Outbound Splitting one table into two tables to use calculation formula for each table.

[Nafa1]

Hello,

I have a table on power bi and would like to split it into two tables to achieve some calculation formula. This is the look for the Main table ( Just small view of it ) :

Table A

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
019-35-005-1008	0.0416655	Ahmed	4/1/2019 5:29:42 PM	1008	6
SP2215	0.004	Ahmed	4/1/2019 5:32:16 PM	1008	5
004-002-008-2459	0.5	Ahmed	4/1/2019 5:34:48 PM	1008	5
019-35-005-1008	0.0416655	Ahmed	4/1/2019 5:29:42 PM	1008	5
033-048-060-3578	0.00347	Ahmed	4/1/2019 5:34:00 PM	1008	6
SP3237	0.004	Moh	4/1/2019 5:32:41 PM	1008	5
SP3237	0.004	Moh	4/1/2019 5:32:41 PM	1008	6
001-013-000-2566	0.15	Ahmed	9/26/2018 12:00:00 AM	SP2215	6
031-45-000-3198	0.08	Ahmed	1/12/2019 5:34:01 PM	SP2215	6
036-000-000-3207	0.001	Ahmed	9/26/2018 12:00:00 AM	SP2215	5
031-65-000-3192	0.77	Ahmed	8/4/2021 9:25:06 AM	SP2215	5
001-013-000-2566	0.15	Ahmed	9/26/2018 12:00:00 AM	SP2215	5
031-45-000-3198	0.08	Ahmed	1/12/2019 5:34:01 PM	SP2215	5

( GROUP ID ) contains different values ( Some of them start with Number and some of them start with letters like SP for example ).

When we filter a number on ( Group_ID ) like let's say for example 1008
We see those data :

TABLE B ( Expected Result )

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
019-35-005-1008	0.0416655	Ahmed	4/1/2019 5:29:42 PM	1008	6
SP2215	0.004	Ahmed	4/1/2019 5:32:16 PM	1008	5
004-002-008-2459	0.5	Ahmed	4/1/2019 5:34:48 PM	1008	5
019-35-005-1008	0.0416655	Ahmed	4/1/2019 5:29:42 PM	1008	5
033-048-060-3578	0.00347	Ahmed	4/1/2019 5:34:00 PM	1008	6
SP3237	0.004	Moh	4/1/2019 5:32:41 PM	1008	5
SP3237	0.004	Moh	4/1/2019 5:32:41 PM	1008	6

But Also , The ITEM_ID Like SP2215 can be a Group_ID too and it contains different items on it :

Table C ( Expected Result )

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
001-013-000-2566	0.15	Ahmed	9/26/2018 12:00:00 AM	SP2215	6
031-45-000-3198	0.08	Ahmed	1/12/2019 5:34:01 PM	SP2215	6
036-000-000-3207	0.001	Ahmed	9/26/2018 12:00:00 AM	SP2215	5
031-65-000-3192	0.77	Ahmed	8/4/2021 9:25:06 AM	SP2215	5
001-013-000-2566	0.15	Ahmed	9/26/2018 12:00:00 AM	SP2215	5
031-45-000-3198	0.08	Ahmed	1/12/2019 5:34:01 PM	SP2215	5

Note that the ( GROUP_ID ) for example ( 1008 ) have more than one ( SP ) on ( ITEM_ID ) .. Like on Group_ID 1008 , it has two ( Values starts with ( SP ) which are ( SP2215 ) and ( SP3237 ) and both can be a ( Group_ID ) and contains several ( ITEM_IDs ) .

( Group_ID ) can starts with any letter like BAK or MIXX . Not Just ( SP ).

The reason i would like to split it into two tables ( Table B and C ) is because I would like to apply a calculation formula for the values starting with a number in ( Group_ID ).

And apply another calculation formula for the items starting with letters like ( SP ).

The calculation is to see the forecasting value for each item.

Let’s say i’m going to buy 1000 Pcs from this item ( From Table B ) :

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
019-35-005-1008	0.0416655	Ahmed	4/1/2019 5:29:42 PM	1008	6

Since the Group id Starts with number , the calculation formula will be like below :
1000 * QTY ( 0.0416655 )

But When the group id starts with letter like SP ( From Table C ) :

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
001-013-000-2566	0.15	Ahmed	9/26/2018 12:00:00 AM	SP2215	6

the formula will be like below :

1000 * SP ( 0.004 ) ( Explained below ) * QTY ( 0.15 )

( for the SP value , it will call it from the Table B and the ITEM_ID should be related with the Group id in order to get the exact value )

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID
SP2215	0.004	Ahmed	4/1/2019 5:32:16 PM	1008	5

Here’s a google sheet link for the Original table
https://docs.google.com/spreadsheets/d/1HHNIceZxn7kX2lr_YyOxmIttgxQj57O7-wymK27HHSg/edit?usp=sharing

Here’s the PPIX file
items1.pbix

Thank you