cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saikumarkatkuri
Frequent Visitor

Rolling count by total for category

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.


 

1 ACCEPTED SOLUTION
v-mengzhu-msft
Community Support
Community Support

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:

vmengzhumsft_0-1657695267604.png

 

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

View solution in original post

3 REPLIES 3
v-mengzhu-msft
Community Support
Community Support

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:

vmengzhumsft_0-1657695267604.png

 

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

Hi @v-mengzhu-msft 

Thank you so much for sending solution. 

Saikumarkatkuri_0-1658283913459.png

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
Frequent Visitor

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors