Help with summing an aggregation. (Count of worst position). (Equivalent of LOD from Tableau)

[bas565]

I have recently moved from Tableau to Power BI and would like some guidance on how to approach an aggregation issue please. In Tableau i would use LOD for this approach, as i have many times in similar scenarios. 

In this case, i have multiple KPIs within a category. I have a National value, and an Area value (North, West, East, South etc).
I want to know for each KPI, if each KPI is green in every Area (ie Nationally might be on target, but that's hiding the issue that South didn't meet target). 

The approach i've taken for similar aggregations is something like {FIXED CATEGORY:  COUNT(WHERE MAX OUTPUT =1)}. (Red=3, Amber= 2, Green=1). 

EXAMPLE:

 
However, if i do this in Power BI, i get Row = 0 (At least one Area not green), + Row =1 (All Areas Green) =0

This calc sums 0 +1 =0

This approach doesn't let me aggregate at all.

Ultimately what i'm trying to get to is something like "in Category: Safety. There are 3 KPIs, of which every KPI is green in all Areas so 3/3"

I've had a suggestion that because PBI works at row level, i need to have my data with each KPI on a row. Currently i have unpivoted it. I ideally don't want two data sources that are formatted differently for different calcs.

Thanks for reading. I've had to anonymise the data for this post so don't have a .pbix file to share unfortunately. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[bas565]

Category	KPI	Date	Target National (Target *CountArea)	Target by Area	Amber	Good	National	North	South	East	West
Safety	Work Incidents	01/04/2023	0	0	0	Smaller	0	0	0	0	0
Safety	Near Misses	01/04/2023	40	10	8	Bigger	59	10	11	15	23
Safety	Fatigue Risk Assessments	01/04/2023	200	50	45	Bigger	246	51	62	55	78
Finance	Variance to budget	01/04/2023	0	0	0	Smaller	-15190	-10000	-500	-1234	-3456
Finance	Sales	01/04/2023	4,000,000	1,000,000	900,000	Bigger	3,919,000	800,000	950,000	1,030,000	1,139,000
Finance	Profit	01/04/2023	400,000	100,000	50,000	Bigger	540,000	135,000	145,000	80,000	180,000
HR	Leavers	01/04/2023	20	5	3	Smaller	3	3	0	0	0
HR	Complaints	01/04/2023	8	2	1	Smaller	0	0	0	0	0
HR	Training Compliance	01/04/2023	90%	90%	85%	Bigger	92%	91%	93%	94%	92%

[bas565]

I will work out how to save the file(s) to a cloud and then publish the link. thanks for your guidance. 
Expected outcome at KPI level:

Expected Outcome at Category Level:

Thanks.