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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    That works, but for some reason, the result that's coming through isn't correct, so it's like a source data issue. I will keep investigating. Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I think i know why it's not giving me the answer it should be....i think the divide formula is also including the '99s' as part of the total, which I want to exclude from the overall calculation

       

      Percent 5-7 Educate Employees =
      DIVIDE([Count of 5-7 Educate Employees],COUNT('Report 1'[Educate employees])
       
      is what I have currently...
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        Is there anything else we can help with regarding this thread?

        Best Regards

        Rena

  • decarsul's avatar
    decarsul
    Helper V
    5 years ago

    Can create a seperate column and do a sum on that.

    =IF(AND('Report1'[Educate employees] >= 5;'Report1'[Educate employees] <= 7);1;0)

     

    This will return a value of 1 when its between 5 and 7, and value of 0 when its not, and will do this on each row.

    Then you can just SUM() the new column