Maximum of sum by category for each customer id

[slsusan]

Hi there, 

I have the following problem:

I have a customer table and a product table. A product can bought by only one customer, i.e. unique products. 

The product table shows the category of the product, the customer who bought the product and the profit which was made by selling the product. 

For each customer, I would like to show the category which the customer is exposed to most, i.e. first get the sum of profit for each category the customer makes purchases in, and then get the category, where the customer has produced the maximum profit with. 

Example in attatched tables: 

Customer 1:  MAX(SUM(profit in cat 1), SUM(profit in cat 3))= MAX(14, 5)=14

Customer 2: MAX(SUM(profit in cat 4))= MAX(2)=2

Customer 3: MAX(SUM(profit in cat 2))= MAX(3)=3

and so on for each customer...

Can you help me to create a measure which outputs the above calculation for the customer table?

I am looking forward to your support! 

Kind regards

Susanne

[parry2k · Accepted solution]

@slsusan assuming you already have a relationship between these two tables on the Customer column which will be on to many, add the following two columns in the Customer table:

Max Profit = 
VAR __t = RELATEDTABLE ( 'Product' )
RETURN
MAXX ( __t, [Profit] )

Most Profitable Category = 
VAR __t = RELATEDTABLE ( 'Product' )
RETURN
CALCULATE ( MIN ( 'Product'[Category] ), TOPN ( 1, __t, [Profit], DESC ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.⚡

[parry2k]

@slsusan what is the expected result for each customer:

for customer 1, will it be category 1 and profit 10, and for customers 2 and 3, just whatever category and profit value since they have only one row of data? Is this what you want?

[slsusan]

Dear @parry2k ,

thank you for your reply. 

For each customer, we want to determine the most profitable category. 

If there is only one category, of which a customer has bought a product, it is clear, that this category is the most profitable one (customer 2 -> category 4, profit=2, cutomer 3 ->category 2, profit=3). 

Each customer is in at least one category, else it would not be a customer anyways. 

Has my situation become more understandable?

Best regards

Susanne 

[parry2k]

@slsusan Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[slsusan]

Dear @parry2k , dear all,

I have forgotten to attatch my example data files, here they are.

Further, I would like to get the max exposed category as well as the profit of it for each customer. 

The categories are supposed to be chosed by a filter.

Thanks! 

Customer tableProduct table