Count by Criteria

[Naveennegi119]

Hi All,

need help in count in criteria:-

<30

>=30 &<70

>=70

Sub	No.
A	20			Count by criteria
A	29			Sub	<30	>=30 & <70	>=70
A	31			A	2	5	3
A	35			B	4	3	3
A	55
A	80
A	85
A	75
A	45
A	40
B	10
B	15
B	17
B	28
B	40
B	45
B	56
B	80
B	91
B	78

It's easy in excel but in power BI i can't figure out this.

Best regards,

NICK

[vanessafvg · Accepted solution]

@Naveennegi119 

Measure > 30 = CALCULATE(COUNTROWS(Table2),Table2[No.] < 30)

Measure 30&70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >= 30, Table2[No.] < 70)

Measure70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >=70)

that should give you the idea

and gives this result

[Naveennegi119]

Thanks for sharing Idea @vanessafvg

Regards,

NICK

[Naveennegi119]

Hi @vanessafvg

Can it possible if criteria range is not filfull condition.

it show 0 instead of blank

Sub	Count	<30	>=30 - <70	>=70
A	10	5		5
B	10	5	3	2

See in A >=30 - <70 catagory

Regards,

NICK

[vanessafvg]

@Naveennegi119 Measure70 = CALCULATE(COUNTROWS(Table2)+0,Table2[No.] >=70)

you can just add +0 

you can also use an if statement with isblank but adding + is easier

[Naveennegi119]

Hi @vanessafvg,

it's working but not usable with filter or slicer.

but thanks for help

Regards,

NICK

[vanessafvg]

@Naveennegi119what are you filtering on?

[Naveennegi119]

@vanessafvg

I'm using Chiclet slicer,

problem is

Selected Apple in slicer
	ChicletSlicer			Sub	<30	>=30 - <70	<=70
	Apple			A	5		5
	Mango			B	5	3	2
	Banana
			Measure >30 = CALCULATE(COUNTROWS('FT Test Result'),'FT Test Result'[Percentage]<30)
After using this formula
Measure >30 = CALCULATE(COUNTROWS('FT Test Result')+0,'FT Test Result'[Percentage]<30)
Selected Apple in slicer
	ChicletSlicer			Sub	<30	>=30 - <70	<=70
	Apple			A	5	0	5
	Mango			B	5	3	2
	Banana			A	0	0	0
				B	0	0	0
				A	0	0	0
				B	0	0	0

showing apple value with 0, that's good

but also show other data value with 0

Regards,

NICK