cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

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

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

parry2k
Super User III
Super User III

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






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
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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors