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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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'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