SPC and Shewart Analysis in Power BI

[Anonymous]

Hi Experts (see images)

I have found the following link on the web, which look at outlayer and variance in data point based on STD to work out the Upper and lower limits and averagex on your data.

Link: https://exceleratorbi.com.au/six-sigma-control-charts-in-power-bi/

Link: https://towardsdatascience.com/how-to-create-a-control-chart-in-power-bi-fccc98d3a8f9 

The above article uses the STD calculation to set the upper and lower limits, the true calculation uses something called the median moving range (Upper Limit: Multiply MEDIAN Moving Range by 3.14 and add product to average)

and 

(Lower Limit: Multiply MEDIAN Moving Range by 3.14 and subtract product from average)

and in order to calculate the Median Moving Range you need to find the differences between each pair of successive values; then sort the differences in numerical order; then identify the middle value.

Has anyone done this...

[RichGillett]

If you are still looking for a solution, this free custom visual does all SPC chart types:

AUS-DOH-Safety-and-Quality/PowerBI-SPC: A free and open-source PowerBI custom visual for SPC charts ...

[Greg_Deckler]

@Anonymous Seems interesting, do you have a set of sample data to work with?

[Anonymous]

Hi Greg

Link: https://www.dropbox.com/s/ztfhicjnxeq5f8k/SPC%20Sample%20Datat.pbix?dl=0 

The sample data is per image on page 70. I have missed out the sorting order from the Power BI File...on purpose....I would like this in DAX. Thanks

[Greg_Deckler · Accepted solution]

@Anonymous So are you saying that you don't want to add an Index column to your table in Power Query but instead want to create an DAX Index? If a DAX Index, then perhaps the Mythical DAX Index? 

The Mythical DAX Index - Microsoft Power BI Community

Here is something I created using that as a guide:

DAX Indexed Table = 
    VAR __SourceTable = 'SPC Chart Data'
    VAR __Count = COUNTROWS(__SourceTable)
    VAR __SortText = CONCATENATEX('SPC Chart Data',[Month] & ":" & [Detections] & ":" & [Range],"|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,__Count,1),
            "Item",PATHITEM(__SortText,[Value],TEXT)
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "Month",LEFT([Item],3),
            "Detections",MID([Item],5,2),
            "Range",RIGHT([Item],LEN([Item]) - FIND(":",[Item],5,0))
        )
RETURN
    SELECTCOLUMNS(__Table1,"Index",[Value],"Month",[Month],"Detections",[Detections],"Range",[Range])

[Anonymous]

Hi Greg, 

I have made progress with the file. I just have one issue not sure how to calculate the Upper and Lower Band Dynamically based on the data set. As you can see i have cheated by putting in a fixed value as a calculated column into the data...

New link

https://www.dropbox.com/s/ztfhicjnxeq5f8k/SPC%20Sample%20Datat.pbix?dl=0

[Greg_Deckler]

@Anonymous Says the item was deleted.

[Anonymous]

Hi Greg, I managed to resolve the issue to hand - hence why i removed the file...

[Anonymous]

Hi Greg

Let me mock something up based on the data given in the screen images above... hang fire SIR