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 1000 0.55 550
TPM 1176 0.20 235.2
TPM 2175 0.92 2001
MGT 47922 0.40 19168.8
MGT 7641 0.39 2979.99
BEW 4617 5.41 24977.97

 

Table 2

Product Quantity Price per Unit Sale Price
2M9 2400 2.83 6792
SHC 808 3.02 2440.16
SHC 2304 4.17 9607.68
PLM 1050 4.1 4305
MGT 11555 0.42 4853.1
MGT 13459 0.42 5652.78
MGT 11191 0.44 4924.04
MGT 11717 0.47 5506.99
MGT 7641 0.54 4126.14
BEW 1900 5.53 10507
BEW 957 5.53 5292.21
BEW 1760 5.86 10313.6
TPM 3 3.23 9.69
TPM 2176 3.6 7833.6
TPM 2175 4 8700

 

I like to have the following end result table

 

Product Quantity (bought) Total purchase cost Quantiy (Sold) Sale Price
2M9 2400 4848.00 2400 6792
SHC 808 3013.84 808 2440.16
SHC 2304 8593.92 2304 9607.68
PLM 1050 3570.00 1050 4305
TPM 3 0.48 3 9.69
TPM 2176 785.2 2176 7833.6
TPM 2175 2001.00 2175 8700
MGT 11555 4622.00 11555 4853.1
MGT 13459 5383.60 13459 5652.78
MGT 11191 4476.40 11191 4924.04
MGT 11717 4686.80 11717 5506.99
MGT 7641 2979.99 7641 4126.14
BEW 1900 10279.00 1900 10507
BEW 957 5177.37 957 5292.21
BEW 1760 9521.60 1760 10313.6

 

Need Assistance to achieve this result. Thank you

  • 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.

9 Replies

  • 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

       

       

  • Anonymous's avatar
    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 )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    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 Anonymous for your reply. This measure is good, but it does not provide all correct answers. For example last row of the ouput

      TPM 2175 7833.60 3.60 2001.92

      Calculation of 2001.92 total purchase cose in the measure is not correct

      Where as  in my end result table 

      TPM 2176 783.36 2176 7833.6

      Total purchase cost is 783.36 

      This purchase cost is calculated from the following rows in table 1

      TPM 1000 0.55 550
      TPM 1176 0.20 235.2

       

      thank you

       

       

  • 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.