COUNT based on categorized criteria(priority basis)

[mariaSam1014]

HI Power community,
This is my first post ever:

Tenant	OccupierId	Location	Weekly _RN	RISK Severity Level	RN_delta	Severity Trend
ABC	11 BAC drive	Bathroom 1	237.33	EXTREME	237.33	Deteriorating
ABC	11 BAC drive	Kitchen	276.86	HIGH	39.52	Deteriorating
ABC	11 BAC drive	Living room	201.00	MEDIUM	-19.00	Improving
ABC	5 Place	Bathroom 1	276.86	HIGH	38.00	Deteriorating
ABC	5 Place	Kitchen	297.29	HIGH	-17.57	Improving
ABC	5 Place	Living room	211.00	MEDIUM	36.86	Steady
ABC	892 cloud place	Bathroom 1	212.00	MEDIUM	7.00	Steady
ABC	892 cloud place	Kitchen	200.43	MEDIUM	7.00	Steady
ABC	892 cloud place	Living room	215.14	MEDIUM	-126.86	Improving
XYZ	63 Hock Avenue	Bathroom 1	200.43	MEDIUM	50.14	Deteriorating
XYZ	63 Hock Avenue	Kitchen	215.14	MEDIUM	36.86	Steady
XYZ	63 Hock Avenue	Living room	284.29	HIGH	122.57	Deteriorating
XYZ	16 cook Place	Bathroom 1	276.86	HIGH	-126.86	Improving
XYZ	16 cook Place	Kitchen	201.00	MEDIUM	50.14	Deteriorating
XYZ	16 cook Place	Living room	237.33	EXTREME	36.86	Steady
XYZ	12 GIX drive	Bathroom 1	338.86	EXTREME	36.86	Steady
XYZ	12 GIX drive	Kitchen	212.00	MEDIUM	-17.57	Improving
XYZ	12 GIX drive	Living room	200.43	MEDIUM	34.57	Deteriorating

I have this table visual in my report and the highlighted cloumns are the categories that need to be considered in this post.
Please note actual data has thousand of rows , here I want to count no of occupier that lie in a category(Mentioned below) and once an occupierID is counted in a category, it should not be counted in other category.

EXPLANATION: the severity (highlighted columns) are assigned based on the occupiers so the counter should count like this

Number of occupier for tenant ABC where:
RISK Severity Level = "Extreme" && Severity Trend = "Deteriorating"
RISK Severity Level = "Extreme" && Severity Trend = "Steady"
RISK Severity Level = "Extreme" && Severity Trend = "Improving"

RISK Severity Level = "High" && Severity Trend = "Deteriorating"
RISK Severity Level = "High" && Severity Trend = "Steady"
RISK Severity Level = "High" && Severity Trend = "Improving"

RISK Severity Level = "Medium" && Severity Trend = "Deteriorating "
RISK Severity Level = "Medium" && Severity Trend = "Steady"
RISK Severity Level = "Medium" && Severity Trend = "Improving"

PLEASE NOTE that my requirement is to count the occupierID only Once in this counter and priority of counter is it should count like above mentioned sequence (extreme first , then High then medium{and second catogory also in same sequence as mentioned}...)
OutputS of above table would look like this

Tenant : ABC
RISK Severity Level	Severity Trend	COUNT OF OCCUPIER ID
Extreme (80%)	Deteriorating	1
Extreme (80%)	Steady
Extreme (80%)	Improving
High (75%)	Deteriorating	1
High (75%)	Steady
High (75%)	Improving
Medium (70%)	Deteriorating
Medium (70%)	Steady	1
Medium (70%)	Improving

Tenant : XYZ
RISK Severity Level	Severity Trend	COUNT OF OCCUPIER ID
Extreme (80%)	Deteriorating
Extreme (80%)	Steady	2
Extreme (80%)	Improving
High (75%)	Deteriorating	1
High (75%)	Steady
High (75%)	Improving
Medium (70%)	Deteriorating
Medium (70%)	Steady
Medium (70%)	Improving

Please i need the best possible way to achieve this outcome.

THANKYOU

[Gabry]

I think you can try with this

calculate(distintcount (occupierID), allexcept(table, risk severity,severity trend)

let me know

[mariaSam1014]

Hey Gabri,
This was the first ever solution  I tried but its giving totally wrong results. (counting everything in every criteria)

[Gabry]

Sounds really strange, could you paste your table not as an image? So I can copy paste

[mariaSam1014]

i have edited the orignal post please check

[Gabry]

Ok this is your formula for calculated column / measure

ID Count = CALCULATE(COUNT('Table'[OccupierId]), ALLEXCEPT('Table', 'Table'[Tenant], 'Table'[Severity Trend], 'Table'[OccupierId]))

please mark as accepted solution

[mariaSam1014]

this is counting every instant of occupier ID whereas my requirement is to count the occupier id only once in sequence of severity trend and severity level. please read my full original post sir I have a criteria based count not only count all instances.
I have already tried dictinct count, count, sumx with count (I need to have a lookup kind of thing which will look up (the nested criteria) or may be COUNTIF but i dont know how should i structure it

[Gabry]

Well man if you just need to count the number of occupier id for tenant, risk level and security this is the formula:

Test =

CALCULATE(DISTINCTCOUNT('Table'[OccupierId]), ALLEXCEPT('Table', 'Table'[Tenant], 'Table'[Severity Trend],  'Table'[RISK Severity Level]))

as I can see it works perfectly.

Example 

Tenant: XYZ

Risk Level: Medium

Trend: Deteriorating

NUMBER OF OCCUPIER 3

[mariaSam1014]

if I may elaborate thsi sir,

the thing wrong with this in my case is:
It is counting a same occupierID in all categories in which they appear for e.g:
11 BAC drive is being counted in EXTREME-DETERIORATING
as well as 11 BAC Drive is being counted in MEDIUM - IMPORVING
and also 11 BAC Drive is being counted in  HIGH-DETERIORATING

whereas (as of my original post) I want 11 BAC drive to be counted in EXTREME - DETERIORATING category ONLY as this is on highest priority.

[Gabry]

Understood sorry, don't know how to do it then

[mariaSam1014]

no problem, i just cant structure it i am not sure if NESTED IFs work here or COUNTIF

[mariaSam1014]

have you seen my required answer table in my orignal post? If you can generate that fro your calculation I would appreciate that
You are giving me simple count each instance, this is not required at all. Thankyou for this help but exactly this is wrong with what actually is required that its counting each instance correctly. Not required.