DAX

Hi Amz_123,

I made one sample for your reference.

1. Create a calculated table.

Table = VALUES(Table1[Year])

2. Create the measures as below.

maxre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=maxy))

midre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=midre))

minre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=miny))

single = CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=SELECTEDVALUE('Table'[Year]))
)

smax = SUMX(ALLSELECTED(Table1[Category]),[maxre])

smid = SUMX(ALLSELECTED(Table1[Category]),[midre])

smin = SUMX(ALLSELECTED(Table1[Category]),[minre])

result = var county =CALCULATE(DISTINCTCOUNT('Table'[Year]),ALLSELECTED('Table'[Year])) 
return
IF(county=1,[single]/[smin],IF(county>1 && county<3,([maxre]/[smax]+[minre]/[smin])/2,IF(county=3,([maxre]/[smax]+[midre]/[smid]+[minre]/[smin])/3)))

Then we can get the result as we need.

For more details, please check the pbix as attached.

Regards,

Frank