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.