split rows

[sa100]

Hi guys,

I have the following two tables (demo data)

Table 1

Prodcut Quantity Unit Price Total Purchase Cost
2M9 2400 2.02 4848
SHC 3112 3.73 11607.76
PLM 1050 3.40 3570
TPM 3 0.16 0.48
TPM 1000 0.55 550
TPM 1176 0.20 235.2
TPM 2175 0.92 2001
MGT 47922 0.40 19168.8
MGT 7641 0.39 2979.99
BEW 4617 5.41 24977.97

Table 2

Product Quantity Price per Unit Sale Price
2M9 2400 2.83 6792
SHC 808 3.02 2440.16
SHC 2304 4.17 9607.68
PLM 1050 4.1 4305
MGT 11555 0.42 4853.1
MGT 13459 0.42 5652.78
MGT 11191 0.44 4924.04
MGT 11717 0.47 5506.99
MGT 7641 0.54 4126.14
BEW 1900 5.53 10507
BEW 957 5.53 5292.21
BEW 1760 5.86 10313.6
TPM 3 3.23 9.69
TPM 2176 3.6 7833.6
TPM 2175 4 8700

I like to have the following end result table

Product Quantity (bought) Total purchase cost Quantiy (Sold) Sale Price
2M9 2400 4848.00 2400 6792
SHC 808 3013.84 808 2440.16
SHC 2304 8593.92 2304 9607.68
PLM 1050 3570.00 1050 4305
TPM 3 0.48 3 9.69
TPM 2176 785.2 2176 7833.6
TPM 2175 2001.00 2175 8700
MGT 11555 4622.00 11555 4853.1
MGT 13459 5383.60 13459 5652.78
MGT 11191 4476.40 11191 4924.04
MGT 11717 4686.80 11717 5506.99
MGT 7641 2979.99 7641 4126.14
BEW 1900 10279.00 1900 10507
BEW 957 5177.37 957 5292.21
BEW 1760 9521.60 1760 10313.6

Need Assistance to achieve this result. Thank you

[Anonymous]

Hi @sa100 ,

I have some doubt.

How did you calculate to get these results? I can understand how a single value like 2M9 and PLM can give correct results. The rest of the values I am calculating according to the calculation of MGT. But didn't get the correct result, so what calculation did you get your TPM of 783.36 based on? It's not right if I calculate it proportionally. Can you provide more details?

The measure2 is incorrect, please provide more derails.

Best Regards
Community Support Team _ Polly

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

[sa100]

Thank you for your reply @Anonymous 

Criteria for the end result

Table 1 is Purchase Table

Table 2 is Sell Table

End result Table got all the rows from Sell Table plus combined/split rows from Table 1 based on Quantity column.

For example in case of TPM Product

Table 1 got four rows

TPM 3 0.16 0.48
TPM 1000 0.55 550
TPM 1176 0.20 235.2
TPM 2175 0.92 2001

Sales Table got three rows

TPM 3 3.23 9.69
TPM 2176 3.6 7833.6
TPM 2175 4 8700

End Result Table

TPM 3 0.48 3 9.69
TPM 2176 785.2 2176 7833.6
TPM 2175 2001.00 2175 8700

Purchase Cost 785.2 of 2176 in End result comes from adding following two rows of Table 1

TPM 1000 0.55 550
TPM 1176 0.20 235.2

I hope that will explain better.

Apologies, there was minor error regarding value 785.2 it was posted as 783.36

[Anonymous · Accepted solution]

Hi @sa100 ,

Create measures.

Measure = 
CALCULATE(MAX('Table'[Purchase Cost]),FILTER(ALL('Table'),'Table'[Product]=SELECTEDVALUE('Table (2)'[Product])&&'Table'[Quantity]=SELECTEDVALUE('Table (2)'[Quantity])))

Measure 2 = var _1=
CALCULATE(SUM('Table'[Purchase Cost]),FILTER(ALL('Table'),'Table'[Product]=SELECTEDVALUE('Table (2)'[Product])&&'Table'[Quantity]<>SELECTEDVALUE('Table (2)'[Quantity])))
var _2=SUMX(FILTER(ALL('Table (2)'),'Table (2)'[Product]=SELECTEDVALUE('Table (2)'[Product])),[Measure])
var _result=_1-_2
var _count=CALCULATE(COUNT('Table (2)'[Product]),FILTER(ALL('Table (2)'),[Measure]=BLANK()&&'Table (2)'[Product]=SELECTEDVALUE('Table (2)'[Product])))
return
IF([Measure]<>BLANK(),[Measure],IF(_count=1,_result,MAX('Table (2)'[Quantity])*MAX('Table (2)'[Price per unit])))

Best Regards
Community Support Team _ Polly

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

[sa100]

Thanks @Anonymous  . This measue is providing correct results for less than three tranaction of same product in purchase table. Thank you for that. My full data contains more than three transactions of same product in purchase table. Good news is that I used runnning toal in power query plus some of your measures to resolve the issue. Thank you

[Anonymous]

Hi @sa100 ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Purchase Cost] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] = SELECTEDVALUE ( 'Table (2)'[Product] )
                && 'Table'[Quantity] = SELECTEDVALUE ( 'Table (2)'[Quantity] )
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[Unit price] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] = SELECTEDVALUE ( 'Table (2)'[Product] )
                && 'Table'[Quantity] <> SELECTEDVALUE ( 'Table (2)'[Quantity] )
        )
    )
VAR _3 =
    MAX ( 'Table (2)'[Quantity] ) * _2
RETURN
    IF ( _1 = BLANK (), _3, _1 )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[sa100]

Thank you @Anonymous for your reply. This measure is good, but it does not provide all correct answers. For example last row of the ouput

TPM 2175 7833.60 3.60 2001.92

Calculation of 2001.92 total purchase cose in the measure is not correct

Where as  in my end result table 

TPM 2176 783.36 2176 7833.6

Total purchase cost is 783.36 

This purchase cost is calculated from the following rows in table 1

TPM 1000 0.55 550
TPM 1176 0.20 235.2

thank you

[Mahesh0016]

@sa100  please can you explain how to calculate Total purchase cost and Quantity Sale Price in result table.

[sa100]

Thank you for you reply @Mahesh0016 

I have fixed the demo tables in the Post

Sale Price in the end result table  come from sale price column in table 2.

Total purchase cost in end result comes from Table 1 based on the quantity of product sold in the table2. For example quanity of MGT product is 47922 in table1. This need to split into 11555,13459,11191 and 11717 as per the quantity of sold in table 2. Then multiplied by unit purchase price (Table 1)

Thank you

[vicky_]

I think you're looking to merge the two tables. Check this page: https://learn.microsoft.com/en-us/power-query/merge-queries-overview