Forum Discussion

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

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.

split rows.pbix46 KB

Anonymous

Not applicable

3 years ago

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 )