topic Re: split rows in DAX Commands and Tips

split rows

sa100 — Wed, 11 Jan 2023 03:07:53 GMT

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

Re: split rows

vicky_ — Mon, 09 Jan 2023 05:13:53 GMT

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

Re: split rows

Mahesh0016 — Mon, 09 Jan 2023 05:25:58 GMT

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

Re: split rows

sa100 — Mon, 09 Jan 2023 07:04:58 GMT

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

Re: split rows

Anonymous — Tue, 10 Jan 2023 06:27:36 GMT

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.

Re: split rows

sa100 — Wed, 11 Jan 2023 00:40:52 GMT

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

Re: split rows

Anonymous — Wed, 11 Jan 2023 01:54:55 GMT

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.

Re: split rows

sa100 — Wed, 11 Jan 2023 03:08:57 GMT

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

Re: split rows

Anonymous — Wed, 11 Jan 2023 04:48:40 GMT

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.

Re: split rows

sa100 — Sun, 15 Jan 2023 21:42:20 GMT

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