Count if greater than measure

[jasonrap]

Newbie here that needs some help.  

I am trying to do some statistical anlaysis of network performance data.  The key attribute I would like to do the analysis on is called values.  Within values are positive whole integers.  Thus far, I can get the min, max, average, and standard deviation.  Through a lookup table, I am able to calculate a value depending on how many standard deviations one is away from the average.  

What I would like to be able to do, is for a given standard deviation value, count how many data points are above that value.  I have tried many different methods to do this, but I keep hitting a wall and cannot get it to work. 

Here is what my current overview tab looks like.  for the CutoffValues, I would like to get a sense of how many rows have values greater than the cutoffvalues.  

The measure Z is computed by:

Z = sum('myTable'[values])-AVERAGE('myTable'[values])/stdev.p('myTable'[values])

CutOffValues = 

CutoffValues = (stdev.p('myTable[values])*sum(SD[SDs to enumerate through])+AVERAGE('myTable'[values]))

[SDs to enumerate through] is just a custom table with values 1 through 10

RoundedZ = ROUND([Z],1)

Greatly appreciate the help!

Thanks, Jay 

[AlB]

Hi @jasonrap 

Assuming no relationship between the two tables, create a measure and place it in the table visual you are showing:

Measure =
CALCULATE (
    COUNT ( 'myTable'[values] ),
    FILTER ( ALL ( 'myTable'[values] ), 'myTable'[values] > [CutOffValues] )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jasonrap]

Thank you for the reply, I really do appreciate it! 

I created the following measure:

CountedValues = CALCULATE(COUNT('myTable'[values]),FILTER(ALL('myTable'[values]),'myTable'[values]>[CutoffValues]))

but no values are shown

Thank you

[richbenmintz]

Hi @jasonrap ,

Any chance you could provide a sample pbix file?

Thanks,

[jasonrap]

Sure, here is a link to the file: https://drive.google.com/file/d/1YB2jAJrENQpHB22biQjdJWHpIAtpTDG-/view?usp=sharing Hopefully that link works.  Thank you! 

[richbenmintz]

Hi @jasonrap,

Looking now

[richbenmintz]

Hi @jasonrap,

try the following

CountedValues = 
    var cv  = (stdev.p('pdom_onprem_connected-sessions_30s_60d'[values])*sum(SD[SDs to enumerate through])+AVERAGE('pdom_onprem_connected-sessions_30s_60d'[values]))
return
    CALCULATE(COUNTX('pdom_onprem_connected-sessions_30s_60d', [values]),
        FILTER(all('pdom_onprem_connected-sessions_30s_60d'),
        'pdom_onprem_connected-sessions_30s_60d'[values]>cv)
        )

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[AlB · Accepted solution]

@jasonrap 

Try  this slightly modified version:

CountedValues V2 =
VAR aux_ = [CutoffValues]
RETURN
    CALCULATE (
        COUNT ( 'pdom_onprem_connected-sessions_30s_60d'[values] ),
        FILTER (
            ALL ( 'pdom_onprem_connected-sessions_30s_60d'[values] ),
            'pdom_onprem_connected-sessions_30s_60d'[values] > aux_
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jasonrap]

Richard and Super User III, thank you both for helping me with this!!! I grealy appreciate the assistance!  Bravo!