Control chart for binary (yes/no) variable

[rubio3003]

Good morning, 

I work in Quality Improvement and control charts are something we use everyday. Creating a control chart for continuous data is quite simple but I am having tremendous difficulty in creating a control chart for a binary variable (e.g. a process: pass or fail). I need to track pass rate overtime (daily, weekly, monthly, yearly) with Upper and Lower Control Limits. 

Any feedback or insight in this matter would be highly appreciated. 

Estefania

[rubio3003]

Thank you for replying and I apologize for not providing enough detail. @Anonymous @Greg_Deckler @amitchandak @DataZoe 

Control charts have 4 componenets, the count of the measure (average), the central line (the average of the data for a prior time period- serves as a baseline) , and upper and lower control limits (+/- 3 sigma line). 

My data does look like this:

SampleID | Date            | Outcome

1              | 2020-01-01 | 1

2              | 2020-01-01 | 0

3              | 2020-01-01 | 1

What I am strugling with is creating the upper and lower control limits (the red lines in the graph below). It would be a breeze if the data was continuous but I don't know if there is an alternative given my binary variable. 

[Greg_Deckler]

@rubio3003 - Shouldn't those be:

Upper Limit = AVERAGE([Outcome]) + 3 * STDDEV('Table'[Output])

Lower Limit = AVERAGE([Outcome]) - 3 * STDDEV('Table'[Output])

Or am I missing something? 

[rubio3003]

@Greg_Deckler  You can only calculate STDEV of continuous variables, mine is a binary variable 😞  

[Anonymous]

This should not be that hard 🙂 

You have to have some data first. Let's say this is what you have, more or less:

SampleID | Date            | Outcome

1              | 2020-01-01 | 1

2              | 2020-01-01 | 0

3              | 2020-01-01 | 1

...

You get the idea. Now, the above is your fact table where you record all the outcomes at the lowest granularity. You also have to have a Date table that will join to Samples[Date]. Date is your date/time dimension. In this table you'll define all the pieces of time you want: months, years, weeks... you name it.

Now, here's a measure that will give you the % of passes/successes:

[Success Rate] = divide( sum( Samples[Outcome] ), countrows( Samples ) )

[Failure Rate] = 1 - [Success Rate]

And that's basically it. The column Outcome should store 0 for fail, 1 for success.

[Greg_Deckler]

@rubio3003 - Are you referring to this: https://en.wikipedia.org/wiki/Control_chart

I would use 1 and 0 to represent your binary states. Other than that, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@rubio3003 ,

pass% = divide(calculate(countrows(Table) ,[process]="pass") ,countrows(Table) )

fail%= divide(calculate(countrows(Table) ,[process]="fail") ,countrows(Table) )

Create a date table for analysis

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[DataZoe]

@rubio3003 I am not very familiar with control charts but I did a quick search and I think I have the idea. If this isn't it, would it be possible to share a small table of what you the data looks like and even a drawing of what the chart is you are trying to create?  Thanks!

I do think for scatter plots in power bi you need measures, which may be the issue here?

For your pass/fail, this can converted to a percentage with:

Things = countrows('Table')

Pass Percentage = divide(caclulate([Things]),'Table'[Pass/Fail]="Pass"),[Things])

now to aggregate by different ranges, you could do something like:

Pass Percentage Avg Per Week = averagex(values('Date'[WeekOf]),[Pass Percentage]))

you can do that by median, min, max, etc.