Control P Chart in PowerBI

DAX DAX Calculations
1

Hi there,

I am in need of creating Control P Chart in PowerBI
I need to build this using Scrap % which is basically sum(defects)/sum(items) plotting this on Line Chart

I need 4 Measures which are as follows Average , Standard Deviation, UCL & LCL

I got the Average and Standard Deviation to populate numbers I want

Avg = Calculate(AVERAGEX(defects_table,DIVIDE(Sum(defects),Sum(items),0)),ALLSELECTED(defects_table))

Std = CALCULATE(
(STDEVX.P(
SUMMARIZE(
defects_table
,date_dimension[WeekOfYear]
,“Defects”
,CALCULATE(Sum(defects)/Sum(items))),[Defects])),ALLSELECTED(defects_table))

I am looking to build UCL and LCL however could not get my head around this
From Youtube I understood the UCL formula to be as follows which was demonstrated on Excel
UCL : AVG+(3 * SQRT(AVG * (1-AVG)/(TOTAL_DEL_QTY/NO_OF_SUB_GROUPS)))

How can I build this or alternative to UCL above in PowerBI ?
I tried creating this but I dont get the values as expected
UCL CALCULATE(([Avg]+[Std]*3),ALLSELECTED(defects_table))

Attached is the sample data

Please guide and thanks in Advance

ControlP.xlsx (10.2 KB)

2

Hi @yendluru.aj, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @yendluru.aj

Regarding your needs, I would recommend that you watch this video on control charts, it includes UCL and LCL in power BI Control Charts in power BI .

Thanks and all the best

Najah

4

Thanks @NajahS… I have created the required Measures needed Control P Chart !

1 Like
5

Great to hear that. :slight_smile:
Regards
Najah