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 Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors