BayesModel to Calculate Mutually Exclusive Buying Behaviour (PowerQuery or Dax)

[Iloveexcel&excelloveme]

Hello Everyone Here,
I'm hoping someone is able to help me solve a few questions on a Data Model I'm working on which are Mutually Exclusive

Basically here are the conditions;
1) Each Customer Segmentation can only buy only 1 Item Segmentation that is assigned to them
2) If Item Code Classification contained both Set & Single then Set is split by 50% and Single is 100% each
2a) If Item Code Classification contained Set then is 100%
2b) If Item Code Classification is Single then is 100% for both

Things I'have done within PowerQuery;

1. Item Segmentation - I did a Percentage Split for each Set
2. Item Code Classification - I did a Percentage Split for each Item Code Classification
3. Grouping Based on Customers & Item Segment
4. Grouping Based on Customers & Item Code Classification

These are the current problem I'm facing;

• Row 6, Set 2 is supposed to have a Set & Single but only Set is available for Customer A, thus "Population Split Via Item Code Classification" should be 100% instead of 50%

(I've Created a Rule in Rule Tab and merge it to the main table, thus it is bringing in 50%)

• Row 3 - 5: "Population Split Via Item Code Classification", as is not logical to have decimal for this column, thus if there is, I've to Roundup/down and minus off with the Population from "Population Split Via Item Segment" and split it against "Population Split Via Item Code Classification"

I can't do this part as I can't find a method to lookup via the column

appreciate any help here

 

[Ken Puls]

Have to be honest, I'm not following this one.

I'm trying to understand the logic of the table on Sheet1 with regards to Sets and population splits.

Which columns are there in the source data, and which are being added via the query? (There are no queries in the workbook).

Can you give me tables that clearly spell out:
-This is what we start with
-This is the desired output based on the table you start with

I think that would really help, as it's not clear from your example file right now.

 

[Iloveexcel&excelloveme]

Hi Ken,

Give me a day or so and let me change the data source as it is sensitive and attached here
Unless i can send the file directly to you whereby the queries are within the file.

 

[Ken Puls]

No problem.

Even a short sample before/after will work. It doesn't need to be in depth at all. So long as we can see a sample of what raw data looks like and the expected outcome for the issue, then we'll be golden.

 

[Iloveexcel&excelloveme]

Hi Ken,
Sorry it took me sometime to re-clean the data and do it over again so i can post it here.

I've capture one of the error and highlighted the right answer in Yellow. Also is the attached file with the queries




Thanks a million

 

[Ken Puls]

No worries.

So if I break this down, I'm thinking that the issue is that you have two different rules depending on whether or not your data contains only pairs or sets and pairs. I looked at creating some logic to do this, but I'm not sure that it would hold across the entire set.

My thought is that you should really be:
-Adding another column to your Rules table that adds the % split for the Pairs only split
-Adding a conditional column that checks the Sets or Pairs
-Merging the data to extract both potentials
-Adding a conditional column to pick which column to use

Sample attached.

 

[Iloveexcel&excelloveme]

Thank Ken

I think im too engross with the 1st set of rules that i overlook to create a 2nd rule for the pair ... Thanks a million again

 

[Ken Puls]

No worries. Believe me I’ve done the same many times! Sometimes it takes another pair of eyes to see a better path.

Glad it’s worked out.


Sent from my iPhone using Tapatalk