Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
5 years ago

Filter a Measure Value

Hello All,

1. I have created a table for Measure Names to use a Filter,(CycleTime1,CycleTime2)

2.Created Two Columns  CycleTime1 = Datediff(Time2,Time1,Sec), CycleTime2 = Datediff(Time3,Time2,Sec)

3.Created a Measure Cycletime 

CycleTime = IF(
HASONEVALUE('MeasureTable'[Measures]),
SWITCH(
VALUES('MeasureTable'[Measures]),
"CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),
"CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),
AVERAGE(CTM_Timestamps[CycleTime1])
))
4. To Achieve Control Chart I have calculated Average,STD Dev,UCL,LCL
Average = CALCULATE(AVERAGEX(CTM_Timestamps,[CycleTime]),ALLSELECTED(CTM_Timestamps))
STDEV = CALCULATE(STDEVX.P(CTM_Timestamps,[CycleTime]),ALLSELECTED(CTM_Timestamps))
UCL = [Average]+[STDEV]*3
LCL = [Average]-[STDEV]*3
5.I have plotted line chart
6. I need a filter to Select UCL and LCL and I tried the below one but i am getting circular error.
UCL Filter = IF(VALUE([CycleTime])>[UCL],"Crossed UCL","Not Crossed")
Please help.
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    harshagraj , Try like

    UCL Filter = IF(([CycleTime])>[UCL],"Crossed UCL","Not Crossed")

     

    or in Average , STDEV copy the calculation CycleTime , instead of using it

    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Hi thanks for the reply. I tried the UCL Filter but getting same error. I didnt understand the 2nd Point.
      User Just need a UCL filter to see which are the spiked ones.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    harshagraj , change these measures like and check

    Average = CALCULATE(AVERAGEX(CTM_Timestamps, IF(
    HASONEVALUE('MeasureTable'[Measures]),
    SWITCH(
    VALUES('MeasureTable'[Measures]),
    "CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),
    "CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),
    AVERAGE(CTM_Timestamps[CycleTime1])
    ))),ALLSELECTED(CTM_Timestamps))

     


    STDEV = CALCULATE(STDEVX.P(CTM_Timestamps, IF(
    HASONEVALUE('MeasureTable'[Measures]),
    SWITCH(
    VALUES('MeasureTable'[Measures]),
    "CycleTime1 ,AVERAGE(CTM_Timestamps[CycleTime1]),
    "CycleTime2 ,AVERAGE(CTM_Timestamps[CycleTime2]),
    AVERAGE(CTM_Timestamps[CycleTime1])
    ))),ALLSELECTED(CTM_Timestamps))

    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Hello Amit unfortunately I am getting NAN errors if i do that 😞