Count if and Sum by Group

[Anonymous]

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.

[v-yiruan-msft · Accepted solution]

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

[kentyler]

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

1. 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
2. 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
3. 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.
4. 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.