Models and options - probability calculations

Helper I

4 years ago

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?

Forum Discussion

Models and options - probability calculations

Related content

ML model in Fabric: Get prediction probabilities

Datatable options

MoM calculation

Calculation Help

Monitor option