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.

Mahesh0016's avatar
Mahesh0016
Super User
3 years ago

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

 

 

  • sa100's avatar
    sa100
    Helper I
    3 years ago

    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