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
CongPham30SVN
Helper I
Helper I

Help with combinations by DAX

Hi all, may I re-explain my request:

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

CongPham30SVN_6-1609341337328.png

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

CongPham30SVN_5-1609341328048.png

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%:

CongPham30SVN_7-1609341355280.png

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%

CongPham30SVN_8-1609341363063.png

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 here: https://drive.google.com/file/d/1uzQ1T7fhZZtzKQv9PkKtBfkUCHoxgvZx/view?usp=sharing

 

Many thanks for your help.

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @CongPham30SVN 

According to your description, I can clearly understand what you want to get.

You said you want to get the combination that will bring you the biggest profit margin value, I think there are many possible combinations. My opinion is that the best way is to experiment with all the possible ways to get the final result. You can take a look at my steps and find if it’s useful:

  1. I create three Matrix to get the rank of the profit margin of the three categories in order to make the combination:

v-robertq-msft_0-1609469604963.png

 

  1. Then I can make the permutation and combination according to the large selections:
  • One List1, one List2:96.43%

屏幕截图 2021-01-01 105401.png

  • Two List1, one List2: 96.43%

v-robertq-msft_2-1609469604969.png

 

  • Two List1, two List2: 95.71%

屏幕截图 2021-01-01 105420.png

  • One List1, two List2:94.55%

屏幕截图 2021-01-01 105429.png

  • Two List1, three List2: 93.00%

屏幕截图 2021-01-01 105437.png

As we can see, when you select “M”, “Blue”, you can get the the biggest profit margin value, which can prove also prove the truth that littlemojopuppy posted: Single filter is always greater than multiple filter.

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

11 REPLIES 11
v-robertq-msft
Community Support
Community Support

Hi, @CongPham30SVN 

According to your description, I can clearly understand what you want to get.

You said you want to get the combination that will bring you the biggest profit margin value, I think there are many possible combinations. My opinion is that the best way is to experiment with all the possible ways to get the final result. You can take a look at my steps and find if it’s useful:

  1. I create three Matrix to get the rank of the profit margin of the three categories in order to make the combination:

v-robertq-msft_0-1609469604963.png

 

  1. Then I can make the permutation and combination according to the large selections:
  • One List1, one List2:96.43%

屏幕截图 2021-01-01 105401.png

  • Two List1, one List2: 96.43%

v-robertq-msft_2-1609469604969.png

 

  • Two List1, two List2: 95.71%

屏幕截图 2021-01-01 105420.png

  • One List1, two List2:94.55%

屏幕截图 2021-01-01 105429.png

  • Two List1, three List2: 93.00%

屏幕截图 2021-01-01 105437.png

As we can see, when you select “M”, “Blue”, you can get the the biggest profit margin value, which can prove also prove the truth that littlemojopuppy posted: Single filter is always greater than multiple filter.

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

CongPham30SVN
Helper I
Helper I

Hi all, may I re-explain my request:

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

CongPham30SVN_0-1609340162687.png

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

CongPham30SVN_1-1609340212017.png

3. 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%:

CongPham30SVN_2-1609340296218.png

4. 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%

CongPham30SVN_3-1609340398359.png

So, the question is: What combination will bring to me the biggest profit margin value ?

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

Something along the lines of this?

littlemojopuppy_0-1609341475713.png

Not quite, it seems. I hope the reult will look like this:

Step5.jpg

 

That's not going to be possible.  You have to anticipate every possible permutation of attributes.  Single, multiple and all values of list1 combined with any and all possible filtered values of list2, etc.

 

And ultimately, the other gentleman is right: the highest value for a single set of filters will always be higher than a mixed bag of them because incorporating other filters will always dilute that margin percentage.

CongPham30SVN
Helper I
Helper I

@Greg_Deckler  I saw your solution on a post, can you help me with this problem?

There's a DAX function for calculating the number of combinations in a set of data.  So that shouldn't be awful to achieve.  Getting the number associated with each combination a lot more difficult.

CongPham30SVN
Helper I
Helper I

Thank all for your help. I'll try to explain my request on the picture below. I want to know what value I got with all combinations can be happen of my Category and Client. The combination can be result of one Category value and one Client value, or two Category value and one Client value, or three Category value and two Client value ...

CongPham30SVN_0-1609307462779.png

 

@CongPham30SVN , Still not very clear. But you should provide slicer, so that users can select the value.

And you can display sum(Table[Normal Cus]) on a card visual

amitchandak
Super User
Super User

@CongPham30SVN ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

parry2k
Super User
Super User

@CongPham30SVN not sure what output you are looking for? Can you provide more details on it?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.