Forum Discussion
Count IF
Hi, Anonymous
You can try the following methods.
Table:
Average = SUMMARIZE ( 'Table', 'Table'[Item], "Average", CALCULATE ( AVERAGE ( 'Table'[%] ), FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) ) ) )Column:
Ball collour = IF ( [Average] < 0.9, "Yellow ball", IF ( [Average] > 1.1, "Red ball", "Green ball" ) )Measure:
Count = CALCULATE ( COUNT ( 'Average'[Item] ), FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" ) )Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Again:
Since there is one yellow ball I made % both including Yellow and not including yellow. Please see file link.https://drive.google.com/file/d/1fw1_03W0Pn_eOG8CdKhOvSiwVrcXrGru/view?usp=sharing
Hello, I have a primary table:
Ball Logics: if(% < 90% Yellow ball, % > 110% Red ball, % between 90% and 110% green ball)
| Date | Item | % | Ball collour |
| 26/04/2022 | Item A | 113,9% | Yellow ball |
| 13/05/2022 | Item A | 97,7% | Green ball |
| 16/05/2022 | Item A | 97,7% | Green ball |
| 18/05/2022 | Item A | 85,0% | Red ball |
| 25/04/2022 | Item A | 100,3% | Green ball |
| 25/05/2022 | Item A | 100,3% | Green ball |
| 27/04/2022 | Item A | 100,3% | Green ball |
| 18/05/2022 | Item A | 81,0% | Red ball |
| 18/05/2022 | Item A | 89,0% | Red ball |
| 26/04/2022 | Item B | 113,9% | Yellow ball |
| 13/05/2022 | Item B | 97,7% | Green ball |
| 16/05/2022 | Item B | 97,7% | Green ball |
| 18/05/2022 | Item B | 85,0% | Red ball |
| 25/04/2022 | Item B | 100,3% | Green ball |
| 26/04/2022 | Item B | 100,3% | Green ball |
| 27/04/2022 | Item B | 91,0% | Green ball |
| 18/05/2022 | Item B | 70,0% | Red ball |
| 18/05/2022 | Item B | 50,0% | Red ball |
From this table, I make a avarage table:
| Average | % | Ball collour |
| Item A | 96,1% | Green ball |
| Item B | 89,5% | Red ball |
I need to count in this Avarage table how many itens appears different of green ball, can you help me?
thanks a lot.
Hi, Anonymous
You can try the following methods.
Table:
Average =
SUMMARIZE (
'Table',
'Table'[Item],
"Average",
CALCULATE (
AVERAGE ( 'Table'[%] ),
FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) )
)
)
Column:
Ball collour =
IF (
[Average] < 0.9,
"Yellow ball",
IF ( [Average] > 1.1, "Red ball", "Green ball" )
)
Measure:
Count =
CALCULATE (
COUNT ( 'Average'[Item] ),
FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" )
)
Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello:
Glad it worked. Are you able to mark my reply with solution or kudos? I appreciate that. Thanks again..