Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

Hi all, the result I want look like this:

Step5.jpg

 

AlB
Super User
Super User

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.