COUNT IF

[Anonymous]

I have two values, the Limit value and the produced value.

I need to do a Count IF and I need to account for all amounts above the Limit.

I tested this calculation here but it is counting all the values and not just the ones above. note below

MeasureCount = IF(SUM('DATA1'[Measure1])>sum('DATA2'[Measure2]),COUNT('DATA1'[Measure1]),BLANK())

what am I doing wrong ?

[Pulkit · Accepted solution]

Hi,

You are trying to run it like a loop and increment the count value which won't work, instead try this:

1. Create a column with new column = IF((Amount - Limit)>0,1,0)

2. SUM[New column]

Let me know if this works.

[Pulkit · Accepted solution]

Hi,

You are trying to run it like a loop and increment the count value which won't work, instead try this:

1. Create a column with new column = IF((Amount - Limit)>0,1,0)

2. SUM[New column]

Let me know if this works.

[jthomson]

The concept of using count as you are doing in relation to measures is a bit confusing - can you post up some sample data and what measure1 and measure2 are actually doing?

[Anonymous]

Notice, in the table above, we have a limit value, for example the value 10, and below we have the monthly values that are varied.

I need to count how many values have passed that Limit 10.

In the month I will have values in 7 days, in those 7 days, how many exceeded the limit?

Example:

DAY | VALUE | LIMIT | COUNTIF (My Calculate Measure)

  1    |    20     |   10    |      1

  2    |    10     |   10    |      0 

  3    |     7      |   10    |      0

  4    |     8      |   10    |      0

  5    |     4      |   10    |      0

  6    |    23     |   10    |      1

  7    |    48     |   10    |      1

 

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a measure

Count greater than limit = 

COUNTROWS(
    FILTER
    ('Table', 'Table'[VALUE] > 'Table'[LIMIT])
)

[Anonymous]

But what about when the limit value is in another table? I did the data modeling between the two.