Forum Discussion
Count if and Sum by Group
Hello there,
I want to do something that seems simple, but I just can't figure it out.
I want to count instances where a value is >= 90% and also separately count values that are below that
I have this data in the table: Production:
| Plant | Material | Production% |
| Plant1 | Material1 | 0.8034 |
| Plant2 | Material2 | 0.9532 |
| Plant1 | Material3 | 0.9804 |
| Plant1 | Material4 | 1.0000 |
| Plant2 | Material5 | 0.7532 |
If Production% is >= 90% (.9000) then count it as 1 pass. If < 90% then count as 1 fail, and sum and display both in a Matrix like this:
| Plant | Pass >=90 | Fail <90 |
| Plant 1 | 2 | 1 |
| Plant 2 | 1 | 1 |
Thanks.
Hi Anonymous ,
You can create the following 2 measures for calculating the counts of instances( >= 90%) and instances( < 90%) separately, and put them on Values tab of matrix:
Pass =
CALCULATE (
COUNTROWS ( 'Production' ),
FILTER ( 'Production', 'Production'[Production%] >= 0.9 )
)
Fail =
CALCULATE (
COUNTROWS ( 'Production' ),
FILTER ( 'Production', 'Production'[Production%] < 0.9 )
)
Best Regards
Rena
2 Replies
- kentyler
Solution Sage
Usually people prefer measures over calculated columns, but this might be a case where a calculated column would come in handy.
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result to check on your steps along the way.
Hi Anonymous ,
You can create the following 2 measures for calculating the counts of instances( >= 90%) and instances( < 90%) separately, and put them on Values tab of matrix:
Pass =
CALCULATE (
COUNTROWS ( 'Production' ),
FILTER ( 'Production', 'Production'[Production%] >= 0.9 )
)
Fail =
CALCULATE (
COUNTROWS ( 'Production' ),
FILTER ( 'Production', 'Production'[Production%] < 0.9 )
)
Best Regards
Rena
