Count number of cases where status changes to a specific status

[TheCman81]

I'm using an older version of PowerPivot for Excel 2010, 11.2.5 I think it is.

I have a PP table called Applications, with a bunch of fields but the 2 in question are AppRef and Status.

So for each stage of an application, it will get a new record for each decision. What I would like to get, is a count of all applications where the status has went from Refer to Complete.

So for example:

AppRef | Decision
1 Open
1 Refer
1 Complete

2 Open
2 Closed

3 Open
3 Refer
3 Continue
3 Complete

4 Open
4 Complete

So from the above example the count would be 2 from AppRef 1 & 3. So in essence, as long as each AppRef group has both Refer and Complete in it.

Thanks

 

[SOQLee]

This is using Excel 2013 PowerPivot.

Loaded table to PowerPivot

[TABLE="width: 148"]
<tbody>[TR]
[TD]AppRef[/TD]
[TD]Decision[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Refer[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Closed[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Refer[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Continue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Complete[/TD]
[/TR]
</tbody>[/TABLE]


Added two calculated columns:

[DVaue]=IF([Decision]="Refer",1,0)+IF([Decision]="Complete",1,0)

[FlagRefCom]=CALCULATE(SUM([DValue]),FILTER(ALL('Table'),Table[AppRef]=EARLIER(Table[AppRef])))

Created a distinct count measure on AppRef:

dCount AppRef:=DISTINCTCOUNT([AppRef])


Drag PivotTable fields as follows:

FlagRefCom ===> FILTERS (apply filter on '2')
AppRef ======> ROWS
dCount AppRef ==>VALUES

[TABLE="width: 154"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SOQLee]

Continuation from my last post...this measure should give the the distinct counts apps that have been 'Refer' and 'Complete'.

RefCom App Count:=CALCULATE([dCount AppRef],FILTER(Table,Table[FlagRefCom]=2))