cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Add Count and Sum to table based on multiple ifs

Hi all,

 

We have a "Discount Table" looking somewhat like this:

 

SupplierIDLanguageIDProductTypeIDWorkAreaIDDiscountSettings
111150%
121250%
121150%
131250%

 

Then we have a "Sales table" looking somewhat like this:

 

JobIDPriceSupplierIDLanguageIDProductTypeIDWorkAreaID
1001001111
1011001212
1021001312
1031001312
1041001211
1051001111
1061001211
1071001211
1081001312
1091001212
1101001212

 

I would like to get a "Job count" and "Price" for each "Setting" based on first table.

 

So I would like an output somewhat like this:

 

SupplierIDLanguageIDProductTypeIDWorkAreaIDDiscountSettingsNumber of JobsPrice
111150%2200
121250%3300
121150%3300
131250%3

300

 

I am not sure how to combine the tables without a "unique key". 

 

So an example for me would be to know the "Price" and "Job count" for each SupplierID=1, where LanguageID=1, ProductTypeID=1, WorkAreaID=1 and DiscountSettings=50%.

 

And then I would just need all possible combinations.

 

Please help! 🙂

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Add Count and Sum to table based on multiple ifs

@PeterStuhr 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Microsoft
Microsoft

Re: Add Count and Sum to table based on multiple ifs

Hi @PeterStuhr ,

 

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] )
    )
)

 

test_Add Count and Sum to table based on multiple ifs.PNG

 

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

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Add Count and Sum to table based on multiple ifs

@PeterStuhr 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper III
Helper III

Re: Add Count and Sum to table based on multiple ifs

Hi @parry2k 

 

Can you show me how you would do it?

 

I am quite new to this!

Highlighted
Microsoft
Microsoft

Re: Add Count and Sum to table based on multiple ifs

Hi @PeterStuhr ,

 

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] )
    )
)

 

test_Add Count and Sum to table based on multiple ifs.PNG

 

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors