Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Count formula

Hello   Power BI novice user here. I'm trying to use the calculate(count...) formula to work out the total number of results that are >=5 and <=7 (i.e. all 5's, 6's, 7's). The dataset has '99' as a...
Need Help
Show and Tell
  • decarsul's avatar
    decarsul
    5 years ago

    CALCULATE(COUNT('Report1'[Educate employees]),'Report1'[Educate employees]>=5 && 'Report1'[Educate employees]<=7)

     

    the && functions as an AND when turning to SQL, which means the filter has to pass BOTH criteria

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I don't think I understand your response, but I'm unable to make that work...

decarsul's avatar
decarsul
Helper V
5 years ago

Can you give me / us a rough sketch of how your table looks?

Can be fictious data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Educate employees
    4
    99
    7
    5
    7
    7
    7
    6
    7
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      this is the formula im using, but doing it this way also includes the '99' which i don't want...

       

      Count of 5-7 Educate Employees =
      CALCULATE(COUNT('Report1'[Educate employees]),'Report1'[Educate employees]>=5)
       
      After this formula I'm running a 'Divide' formula to convert the figures into percent. That part of the formula works fine. I've validated my result from the 'calculate' formula within excel so I know what result I should receive...
      • decarsul's avatar
        decarsul
        Helper V
        5 years ago

        CALCULATE(COUNT('Report1'[Educate employees]),'Report1'[Educate employees]>=5 && 'Report1'[Educate employees]<=7)

         

        the && functions as an AND when turning to SQL, which means the filter has to pass BOTH criteria