Forum Discussion

atowriss1's avatar
atowriss1
Advocate I
3 years ago
Solved

Creating Measure on Column based on matching 2 columns from 1 table to 2 columns in another

Hi,

 

I am trying to calculate reserve costs for products by entity and porduct.  I have a reserve costs tabe and master sales table.  I want to calulate reserve costs by multiplying reserve costs * # of contracts if the legal entity and product match up from the sales table to the reserve table.

 

What is the best way to accomplish this?  In excel it is using v-look-up, but my actual file is large and the calculations tke forever, so I want to put in PowerBi for efficiency and flexibility in analysis

 

Below is an example of what my tables look like:

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi atowriss1 ,

    You can create a measure as below to get it, please find the details in the attachment.

    Measure = 
VAR _selproduct =
    SELECTEDVALUE ( 'Mast Customer Contract'[Product] )
VAR _sellentity =
    SELECTEDVALUE ( 'Mast Customer Contract'[Legal Entity] )
VAR _ccount =
    SUM ( 'Mast Customer Contract'[Contract Count] )
VAR _ramount =
    CALCULATE (
        SUM ( 'Reserve'[Amount/Contract] ),
        FILTER (
            'Reserve',
            'Reserve'[Product] = _selproduct
                && 'Reserve'[Admin Entity] = _sellentity
        )
    )
RETURN
    _ramount * _ccount

    In addition, you can create a calculated column as below to get it:

    Column =
VAR _ramount =
    CALCULATE (
        SUM ( 'Reserve'[Amount/Contract] ),
        FILTER (
            'Reserve',
            'Reserve'[Product] = EARLIER ( 'Mast Customer Contract'[Product] )
                && 'Reserve'[Admin Entity] = EARLIER ( 'Mast Customer Contract'[Legal Entity] )
        )
    )
RETURN
    'Mast Customer Contract'[Contract Count] * _ramount

    Best Regards

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    atowriss1 , New column in table 2

     

    Sumx(Filter(Table1, Table1[Enity] = Table2[Enity] && Table1[Product] = Table2[Product] ), Table1[Amount])

     

    or

     

    maxx(Filter(Table1, Table1[Enity] = Table2[Enity] && Table1[Product] = Table2[Product] ), Table1[Amount])

     

     

    refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi atowriss1 ,

    You can create a measure as below to get it, please find the details in the attachment.

    Measure = 
VAR _selproduct =
    SELECTEDVALUE ( 'Mast Customer Contract'[Product] )
VAR _sellentity =
    SELECTEDVALUE ( 'Mast Customer Contract'[Legal Entity] )
VAR _ccount =
    SUM ( 'Mast Customer Contract'[Contract Count] )
VAR _ramount =
    CALCULATE (
        SUM ( 'Reserve'[Amount/Contract] ),
        FILTER (
            'Reserve',
            'Reserve'[Product] = _selproduct
                && 'Reserve'[Admin Entity] = _sellentity
        )
    )
RETURN
    _ramount * _ccount

    In addition, you can create a calculated column as below to get it:

    Column =
VAR _ramount =
    CALCULATE (
        SUM ( 'Reserve'[Amount/Contract] ),
        FILTER (
            'Reserve',
            'Reserve'[Product] = EARLIER ( 'Mast Customer Contract'[Product] )
                && 'Reserve'[Admin Entity] = EARLIER ( 'Mast Customer Contract'[Legal Entity] )
        )
    )
RETURN
    'Mast Customer Contract'[Contract Count] * _ramount

    Best Regards