Forum Discussion
Helper IHelp 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:
2. With this data set, I can calculate profit margin of each row:
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%
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.
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:
- I create three Matrix to get the rank of the profit margin of the three categories in order to make the combination:
- Then I can make the permutation and combination according to the large selections:
- One List1, one List2：96.43%
- Two List1, one List2: 96.43%
- Two List1, two List2: 95.71%
- One List1, two List2：94.55%
- Two List1, three List2: 93.00%
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.
11 Replies
- parry2k
Super User
CongPham30SVN not sure what output you are looking for? Can you provide more details on it?
- amitchandak
CongPham30SVN ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos. 
CongPham30SVNThank 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 ...
- amitchandak
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
- CongPham30SVN
Greg_Deckler I saw your solution on a post, can you help me with this problem?
- littlemojopuppy
Community Champion
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
Hi all, may I re-explain my request:
1. I have source table with diffirent value of three list, revenue and profit like this:
2. With this data set, I can calculate profit margin of each raw:
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%:
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%
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
- littlemojopuppy
Something along the lines of this?
- CongPham30SVN
Not quite, it seems. I hope the reult will look like this:
- v-robertq-msft
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:
- I create three Matrix to get the rank of the profit margin of the three categories in order to make the combination:
- Then I can make the permutation and combination according to the large selections:
- One List1, one List2：96.43%
- Two List1, one List2: 96.43%
- Two List1, two List2: 95.71%
- One List1, two List2：94.55%
- Two List1, three List2: 93.00%
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.
