Models and options - probability calculations

[vpsoini]

Despite of the title, this is not about pretty ladies/gentlemen and fancy stock options and your chances for those... 😉

but...

I would need to generate a simple table, which shows that with options are how likely chosen to different models. You can think about your favorite car brand's models as models and common options like AC, tinted windows or sunroof as options.  Even the option is "the same" , it's still different HW per model.  So the question might be "how likely the sunroof is chosen to model B"

My source is a basic sales lines table, with has the info per order, which model is sold with which options. They are linked together with same ordernumber (table in image on right). I also have an option table (bottom left in the image), where I have item number for the option per model (as mentioned before same "sunroof" has item code ABC for Model A, but CBA for Model B. I, however, would like to have those "sunroofs" listed in one column , where each row is for different model  (table in image on top left)

The common field for binding options and rows in sales lines table is ordernumber, but also filling down serial number is possible, if needed (grayed out in the table).

I can calculate the propability% (how like this option has been selected for this model) of each option per model separately by creating a measure like this:

Option1_for_M100 =

   var M100 = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',CONTAINSSTRING('SalesLines'[ItemNo],"M100")),"New",[Order_No]))

   var Option1 = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',[ItemNo]="1234"),"New",[Order_No]))

   var TotalM100 = CALCULATE(COUNTROWS('SalesLines'),CONTAINSSTRING('SalesLines'[ItemNo],"M100") )

Return

   DIVIDE(COUNTROWS(INTERSECT(M100,Option1)),TotalM100)

but that would yield LOTS of measures, since I have 10+ models and 20+ options together.  And then I would also need to build that table manually from separate card values... painful...

Is there a nice way to replace that bolded "M100" and "1234" from that measure so, that it would just fetch that number from the option table, depending which model's row that measure is calculating? Example when calculating "option1 for M200", those bolded values would be "M200" and "4321". So instead of having measure Option1_for_M100, I just have measure "Option1", which, when put into table/matrix visual, would calculate the percentage model by model ( = line by line) into my table like shown in image's top left table?  In ideal world those bolded numbers would change row by row so that models, options and item numbers would still be in sync?

Btw, %-values in my example table are just out of this air, so they're there just for visual purposes...

This could be an easy solution here, but I'm just having a "monday-freeze" in my brain...

Thanks a lot in advance for your replies

[v-henryk-mstf]

Hi @vpsoini ,

Can you provide screenshots of the desired results and describe them so that I can do further testing? Looking forward to your reply.

Best Regards,
Henry

[vpsoini]

Hi @v-henryk-mstf 

The outcome would be table like this:

which show the share (propability) of an option selected for different models. So the table would have columns "Model", "Option1", "Option2", "Option3"... etc and in different rowst there would be different models.

I would like to have one measure per option so, that when calculating each row, the measure would place both the model and corresponding option's item number into calculation and would calculate that percentage. 

I have highlighted 2 cells from that sample table above and corresponding item numbers from item table below.  In the desired solution, the red square would be calculated by measure called "option1"  by using variables Model = M100 and corresponding option item number from option item table = 1234 in the calculation:

Option1=

   var count_option = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',CONTAINSSTRING('SalesLines'[ItemNo],"M100")),"New",[Order_No]))

   var option = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',[ItemNo]="1234"),"New",[Order_No]))

   var Total= CALCULATE(COUNTROWS('SalesLines'),CONTAINSSTRING('SalesLines'[ItemNo],"M100") )

Return

   DIVIDE(COUNTROWS(INTERSECT(count_option,option)),Total)

and when calculating value higlighten with green the measure for option2 would look like:

Option2=

   var count_option = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',CONTAINSSTRING('SalesLines'[ItemNo],"M400")),"New",[Order_No]))

   var option = DISTINCT(SELECTCOLUMNS(FILTER('SalesLines',[ItemNo]="8642"),"New",[Order_No]))

   var Total= CALCULATE(COUNTROWS('SalesLines'),CONTAINSSTRING('SalesLines'[ItemNo],"M400") )

Return

   DIVIDE(COUNTROWS(INTERSECT(count_option,option)),Total)

Hopefully that helped?