Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

COUNT IF

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...
  • Pulkit's avatar
    Pulkit
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Create a measure

     

    Count greater than limit = 

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

     

     

     

     

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

Create a measure

 

Count greater than limit = 

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

 

 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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