cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.  

OverviewTab.PNG

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

@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 

SU18_powerbi_badge

View solution in original post

8 REPLIES 8
Super User II
Super User II

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 

SU18_powerbi_badge

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
OverviewTab2.PNG
Thank you

Hi @jasonrap ,

 

Any chance you could provide a sample pbix file?

 

Thanks,

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

Hi @jasonrap,

 

Looking now

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!

 

@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 

SU18_powerbi_badge

View solution in original post

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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