Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add Count and Sum to table based on multiple ifs

Hi all,   We have a "Discount Table" looking somewhat like this:   SupplierID LanguageID ProductTypeID WorkAreaID DiscountSettings 1 1 1 1 50% 1 2 1 2 50% 1 2 1 1 50% ...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous concatenate columns to create a surrogate key to set the relationship between tables and then you can get data from the table pretty easily.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Try create measures like below:

     

    Price =
CALCULATE (
    SUM ( 'Table (2)'[Price] ),
    FILTER (
        'Table (2)',
        'Table (2)'[SupplierID] = MAX ( 'Table'[SupplierID] )
            && 'Table (2)'[ProductTypeID] = MAX ( 'Table'[ProductTypeID] )
            && 'Table (2)'[WorkAreaID] = MAX ( 'Table'[WorkAreaID] )
            && 'Table (2)'[LanguageID] = MAX ( 'Table'[LanguageID] )
    )
)

Job count=
CALCULATE (
    COUNT( 'Table (2)'[JobId] ),
    FILTER (
        'Table (2)',
        'Table (2)'[SupplierID] = MAX ( 'Table'[SupplierID] )
            && 'Table (2)'[ProductTypeID] = MAX ( 'Table'[ProductTypeID] )
            && 'Table (2)'[WorkAreaID] = MAX ( 'Table'[WorkAreaID] )
            && 'Table (2)'[LanguageID] = MAX ( 'Table'[LanguageID] )
    )
)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi Anonymous ,

 

Try create measures like below:

 

Price =
CALCULATE (
    SUM ( 'Table (2)'[Price] ),
    FILTER (
        'Table (2)',
        'Table (2)'[SupplierID] = MAX ( 'Table'[SupplierID] )
            && 'Table (2)'[ProductTypeID] = MAX ( 'Table'[ProductTypeID] )
            && 'Table (2)'[WorkAreaID] = MAX ( 'Table'[WorkAreaID] )
            && 'Table (2)'[LanguageID] = MAX ( 'Table'[LanguageID] )
    )
)

Job count=
CALCULATE (
    COUNT( 'Table (2)'[JobId] ),
    FILTER (
        'Table (2)',
        'Table (2)'[SupplierID] = MAX ( 'Table'[SupplierID] )
            && 'Table (2)'[ProductTypeID] = MAX ( 'Table'[ProductTypeID] )
            && 'Table (2)'[WorkAreaID] = MAX ( 'Table'[WorkAreaID] )
            && 'Table (2)'[LanguageID] = MAX ( 'Table'[LanguageID] )
    )
)

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.