Averagering over all but filtering at the same time

[illio]

Hi everyone,

Quite new to PowerPivot here and I'm having quite a number of issues - especially wrt. DAX.

A little background: We have a data warehouse with all changes to cases, such that a case has, e.g., a department, a case handler, a timekey, a subjectgroup etc. For each CaseID there are n different instances in the FactCases table, each with a Revision nr and a status (Open/Closed .. + some more I'll ignore here).

What I currently need to do is:

1. Determine the average completion time (time to progress from 1st Open to Closed status) for all cases.

.. or, if possible, even better:

2. Determine the average completion time per department, but not per case handler or per case.

I've slightly given up on nr. 2 after fiddling with ALL and ALLExcept for what felt like ages. But I thought I had nr 1 down with this:

=CALCULATE(AVERAGE(FactCases[CompletionTime]); ALL('FactCases'); FILTER(FactCases; FactCases[Status] = "Closed"))

However that doesn't work. The Filter seems to cancel ALL.. I also tried with ALLEXCEPT, with Status added as an exception. Didn't work either.

Basically what should have resulted in a fixed value becomes dependent on the CaseID in the PivotTable. And some cases aren't closed, so for those it becomes a blank value.

Any ideas?

 

[illio]

Also, I should maybe clarify one thing: CompletionTime is computed for each row as the time used up to that change. So by filtering the cases by those with Status="Closed" you get the full completion time for the caseID.

 

[akice]

Do you have some sample data you can publish? what you are looking for shouldn't be very hard to do - but need a clearer understanding of what tables you are using,how they are related, and what you are putting on rows/columns and sample data to really help.

As far as this comment:

...However that doesn't work. The Filter seems to cancel ALL...

You are mostly correct. The first parameter to CALCULATE is evaluated last after all the other filter parameters are applied to the model. The code

 [COLOR=#574123]ALL('FactCases'); FILTER(FactCases; FactCases[Status] = "Closed")[/COLOR]

are logically 'AND' together after they are evaluated. The first part ALL('FactCases') removes any filters on the whole FactCases table. But the second parameter turns around and iterates over all the rows visible in the current filter context and further reduces them to rows where FactCases[Status] = "Closed". Intersect them together and you have your evaluation context applied to the model for the AVERAGE function to calculate. So in the end the ALL('FactCases') has no effect in this particular case. What you did is equivalent to this shorter version:

[COLOR=#574123]CALCULATE(AVERAGE(FactCases[CompletionTime]); Filter( FactCases; FactCases[Status] = "Closed"))[/COLOR]

Like i say if you can post some sample table/data I or someone else should be able to help...

 

[illio]

That would be great. Thank you.
So we have FactCases which looks like the following:






KLEKey, FacetKey, TimeKey, HandlerKey, DepartmentKey, CaseTypeKey, CaseID, Revision, Title, Status, TimeSinceCreation, TimeSinceModification, Timestamp, CompletionTime
2231, 30, 201602110809, 3, 2, 2, BOG-2015-00034-004, 5, PPV vurdering, Open, 43321103, 43261103, 11-02-2016 08:09:00, 97
2231, 30, 201602110809, 3, 2, 2, BOG-2015-00034-004, 6, PPV vurdering, Open, 43375043, 43255043, 11-02-2016 08:09:00, 97
2231, 30, 201602110810, 3, 2, 2, BOG-2015-00034-004, 7, PPV vurdering, Closed, 43385993, 43265993, 11-02-2016 08:10:00, 1


Such that each case has a CaseID and FactCases holds all changes to that case. In this case we show revisions 5-7, where the case is closed on revision 7. Completion time for each row is the number of days it's been open (if it's open) or the time since it was created (if its closed). So that's why there's the oddness of 97 days on revision 6, but 1 on revision 7 (this is historical test data).


What I need to do is get the average completiontime for closed case revisions only. I need it either as a global average for closed cases or dependent on the DepartmentKey, such that I get an average for each Department.. The latter is preferred, but I'm even less sure how to do that .


I hope someone can help. Thanks once again.

 

[anvg]

Hi

I need it either as a global average for closed cases

It is like what you did but simpler

=CALCULATE(AVERAGE(FactCases[CompletionTime]); FILTER(ALL('FactCases'); FactCases[Status] = "Closed"))

or dependent on the DepartmentKe

=CALCULATE(AVERAGE(FactCases[CompletionTime]); FILTER(ALLEXCEPT('FactCases'; FactCases[DepartmentKey]); FactCases[Status] = "Closed"))

Regards,

 

[illio]

Thanks so much. That did the trick

 

[illio]

Actually, one other question, if you have time.

I used the above to create a KPI, showing how much the case completion time differed from the average case completion time and then created a PivotTable with:

Rows: CaseID, Department Name, Case Handler Name
Values: Completion time, KPI

Problem is that while a given case may only have one department that did any work and only one case handler in there as well (leading to 1 value for completion time) it shows as many rows as there are combinations of department and casehandler, per caseID. So, e.g.,

CaseID	Department Name	CaseHandler Name	Completion time	Status (KPI)
EMN-2016-00007 <tbody> </tbody>	PPF <tbody> </tbody>	Anders Andersen <tbody> </tbody>	10	red
		Bent Bendtsen
		Charles Charlson
	KGH	Anders Andersen <tbody> </tbody>
		Bent Bendtsen
		Charles Charlson
EMN-2016-00009	PPF	Anders Andersen
		Bent Bendtsen	7	yellow
		Charles Charlson
	KGH	Anders Andersen
		Bent Bendtsen
		Charles Charlson

<tbody>
</tbody>

How do I stop it from doing this? It should only show options that actually have data? I checked to see if the "Show items with no data on rows" or "Show items with no data on columns" were checked, but they weren't. My best guess is that this happens due to Completion time being a computed field / measure? Is that a possible reason? And how do I solve it.

 

[anvg]

I used the above to create a KPI

I am sorry, illio, but I did not read anything about KPI yet.
Regards,

 

[illio]

I managed to solve the KPI issue, but that did however bring me back to an issue from before. I'm trying to calculate the average case completion time for each case handler. In this particular case we have 4 case handlers, two of which have actually completed a case and 2 who have not. The two that have completed their cases in 1 day. So you would expect the results to be Avg. Case completion: 1,1,blank,blank respectively. Instead what I get is 1,1,1,1 .. So I seem to be doing something wrong. This is my DAX formula:

AvgCaseCompletionTimePerCaseHandler:=ROUNDUP(CALCULATE(AVERAGE(FactCases[CompletionTime]); FILTER(ALLEXCEPT('FactCases'; FactCases[HandlerKey]); FactCases[Status] = "Closed")); 0)

Any ideas?