Forum Discussion

sa100's avatar
sa100
Helper I
3 years ago
Solved

split rows

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 1...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
sa100
Helper I
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    sa100
    Helper I
    3 years ago

    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