cancel
Showing results for
Did you mean:
New Member

## Count if greater than measure

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
1 ACCEPTED SOLUTION

Accepted Solutions Super User II

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 8 REPLIES 8 Super User II

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 New Member

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 Solution Sage

Hi @jasonrap ,

Any chance you could provide a sample pbix file?

Thanks,

New Member Solution Sage

Hi @jasonrap,

Looking now Solution Sage

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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up! Super User II

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 New Member

Richard and Super User III, thank you both for helping me with this!!! I grealy appreciate the assistance!  Bravo! Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (1,493)