Rolling count by total for category

[Saikumarkatkuri]

Hi there,

I have been researching this a lot and so far have not found any way of getting the result needed.

I have a data set to of job applications and their status and a index table for order/rank of each category.

Eg: 
Index table:
Status Name     Index

Declined              1
long listed           2
shortlist               3
interview            4
offer sent            5
hired                   6

actual data set as below
Job req ID                application status
201                            Declined

203                            Hired
204                            offer sent

207                            Declined
209                            interview

Expecting results as below
applications  status           result rolling count
Total applications            5
Declined                          2
long listed                       3
shortlist                           3
interview                         3
offer sent                         2
hired                                1

If someone could point me in the right direction of how to calculate the rolling count of total, that would be great. To assist, the above sample explains what I am looking for. 

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Saikumarkatkuri ,

please try this measure:

Measure = var _declined=countrows(FILTER('actual data','actual data'[application status]="Declined"))
var _others= COUNTROWS(FILTER(all('actual data'),RELATED('index table'[index])>=SELECTEDVALUE('index table'[index])))
return IF(SELECTEDVALUE('index table'[status name])="Declined", _declined,_others)

the result will be as my testing shows:

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Saikumarkatkuri]

Hi @Anonymous 

Thank you so much for sending solution. 

Is there a way to make the blank as overall applications?
Field application status is from index table. 
index  Application status running total
0           all application      23182

[Saikumarkatkuri]

 @amitchandak