cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CongPham30Shine
Helper I
Helper I

Need help with all combinations can be happen

Hi all, please have take a look and help me about combinations by DAX:

1. I have source table with diffirent value of three lists, revenue and profit like this:

Source.jpg

2. With this data set, I can calculate profit margin of each row:

Step1.jpg

 

3. But if I choose, List 1: size L and M, List 2: color Blue and Green, I will have a combination, and bring profit margin = 92.41%

4. Or if I choose diffirent: List 1: size L and M, List 2: color Blue and Green, List 3: type Coat and Skirt, they will bring profit margin = 97.86%

Step2.jpg

 

Step4.jpg

 

As you see, we can generate many diffirent combinations with these three lists, and each of them will bring another profit margin value. And the question is: What combination will bring to me the biggest profit margin value ?

The file is here: https://drive.google.com/file/d/1uzQ1T7fhZZtzKQv9PkKtBfkUCHoxgvZx/view?usp=sharing

Many thank for all your help.

6 REPLIES 6
CongPham30Shine
Helper I
Helper I

Hi all, the result I want look like this:

Step5.jpg

 

AlB
Super User III
Super User III

@CongPham30Shine 

I'm not sure I understand what  you are trying to do. The max profit margin is obviously the max in the second table you show (M, Blue, Coat at 98.75%).  Any other combination will be lower than that, as it is just a weighted average of the different profit margins

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

@AlB, yup, if simple I choose one value at List 1, one value at List 2 and one value at List 3, M, Blue, Coat will bring the biggest profit margin. But the problem here is I can choose more than one value at three lists, and we got many many combination and may profit margin value.

@CongPham30Shine 

That profit margin, 98.75%, is the max you can get. Period. Any other combination , irrespective of how many values you choose in each of the lists, will yield a lower profit margin.

Now, if you want to look for the maximum profit margin with restrictions (for example, at least two values in List 1) then you have to state that clearly.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

@AlB 

I have no restriction when choosing value in three Lists. With List 1, I can choose 1, 2 or 3 value. It will create many combinations, and with each of them, I keep choosing 1, 2 or 3 value in List 2. So, it will create many many combinations and bring other diffirent profit margin value.

@AlBMaybe with another option, when I choose other combination, I will have diffirent profit margin value, such as 98%. I want to find out: what combination will bring to me this value.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors