Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
104 | |
95 | |
70 |
User | Count |
---|---|
173 | |
134 | |
132 | |
101 | |
95 |