Calculate Count formula

[aaronvincentnz]

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 response (essentially 'Not sure' if converted as 'string') and I want to exclude these as part of the calculation.

Hoping someone can assist.

Thanks

[decarsul]

Not entirely sure what you mean, but since i'm searching for help myself, i figured maybe i can assist in this.

Have you tried something like:

Calculate(count(ID);[result] >= 5 && result <=7)?

[aaronvincentnz]

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

[decarsul]

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

Can be fictious data.

[aaronvincentnz]

Educate employees
4
99
7
5
7
7
7
6
7

[aaronvincentnz]

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 · Accepted solution]

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

[decarsul]

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

[aaronvincentnz]

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

[aaronvincentnz]

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...

[v-yiruan-msft]

Hi @aaronvincentnz ,

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

Best Regards

Rena

[aaronvincentnz]

Nah I think it's all sorted. Thanks