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

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 ?

1 ACCEPTED SOLUTION
Community Support

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:

1. 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.

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 11
Community Support

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:

1. 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.

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.

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:

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 ?

Super User I

Something along the lines of this?

Helper I

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

Super User I

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.

Helper I

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

Super User I

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.

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

Super User IV

@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

Proud to be a Super User!

Super User IV

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

Proud to be a Super User!

Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Microsoft Business Applications Summit sessions

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