UCL/LCL with filter

[alevandenes]

Good morning,

I have quite an interesting challenge that me and my colleagues cant solve. I hope someone here can help me.

I would like to add Upper Control Limit, Lower Control Limit and average line to a column chart.  The goal is to monitor specific issues on product and check when they are out of control.

I have managed to obtain what i need by creating a calculated table and mentioning already in there the filters i want to apply - see below:

Table 7 = SUMMARIZE('Date',[YearMonthnumber], "units", [units produced], "problems with product", CALCULATE(distinctCOUNT(problems with product[COMPLAINT_NUMBER]), problem with product[COMPLAINT_PHASE]="Closed", 'Countries to consider'[considered in:]="Yes", 'ModelstoInclude'[Consider]="Yes"))

 

However, this is not good enough for us because we want to be able to filter the graph by problem type. However, i am not able to specify the problem type already when i am creating the table cause it could be hundreds of different things. 

is there anyone that knows how i can filter by problem type my graph?

Kind regards

[v-shex-msft]

HI @alevandenes,

AFAIK, current power bi does not support create dynamic calculated column/table based on filter effects, they do not work on the same level and you can't use the child to affect its parent.

For this scenario, you may need to add 'problem type' as category to the summarize table with different results based on the 'problem type' group. Then you use filter on the result table based on problem type.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[alevandenes]

Hi, thanks i understand. Is there maybe another way I can obtain this that it is not creating a table? this is the solution i found, but i am actually okay with changing everything completely if i can obtain what i need.

[v-shex-msft]

HI @alevandenes,

Perhaps you can try to create the basic summary table with all value result, then you can create measure expression to extract the total value and calculate with the percent of filter selection and total items.

Regards,

Xiaoxin Sheng