Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
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
@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
Proud to be a Super User!
@Naveennegi119what are you filtering on?
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |