Powerpivot: Sum of minimum values per person per date

Question

I have PowerPivot table data as such

Employee  |   Date   |  Status   |  TimeDifference
-------------------------------------------------------
User A    | 7/1/2015 | Available |  0.00645833333692281
User A    | 7/1/2015 | On Call   |  0.00343750000320142
User A    | 7/1/2015 | On Email  |  0.00215448978012365
User B    | 7/1/2015 | Available |  0.00315548987780541
User B    | 7/1/2015 | On Call   |  0.00421156548977899
User B    | 7/1/2015 | On Email  |  0.00511123354664788

What I need to determine, is the minimum TimeDifference value that occurs for each user, for each date. I cannot simply use Min(TimeDifference) and rely on the PivotTable to divvy it up because I also need the sum of all of those user/daily minimums to determine averages, as well as keep the table summary values accurate.

I have tried several variations of MinX and SumX along with a column combining the Employee and Date to generate a distinct group, but the values do not come out correctly.

Any help on this would be greatly appreciated!

Gordon K Gordon K · Accepted Answer · 2015-07-23T08:28:15

The example below shows a pivottable with two sets of values. The first set is the simple Employee Minimium which gives incorrect totals. The second set shows an Employee Minimum which takes account of the totals. The key to this is to create a measure that identifies whether you are looking at a single value for the employee and if you are not, apply a SUMX based on a column that provides a single instance of each Employee. The Values function is very useful for this type of analysis.

[Employee Minimum]:=MIN(Table1[TimeDifference])

[Employee Minimum Fixed Total]:=if(HASONEVALUE(Table1[Employee]),[Employee Minimum],SUMX(VALUES(Table1[Employee]),[Employee Minimum]))

EDIT: Further information Provided

If you add in additional dates and put the dates on the pivottable, then the totals will work within the date grouping. How things are currently set up, though, the Grand Total ignores the date and gives the total of the lowest values for each of the employees. Is this the way you want it to work, or do you need something else for the grand totals?

Further Edit: Multi-Tier Totals

Ok, further to your comment, you're asking for a complicated hierarchy of totals as each level is looking for the SUM of a set of MINIMUMS, so it can be achieved, but you need to introduce a hierarchy of measures:

Leave the [Employee Minimum] as it is. Then, as your lowest level of interest is the date, you need a measure that works out the minimum for a single date or adds up the minima if you are looking at multiple dates (as you will be within your weekday):

[Employee Minimum Fixed Weekday]:=if(HASONEVALUE(Table1[Date]),[Employee Minimum],SUMX(VALUES(Table1[Date]),[Employee Minimum]))

Now you need to take care of the sum of the minimums for each user across the weekdays:

[Employee Minimum Fixed User]:=if(HASONEVALUE(Table1[Weekday]),[Employee Minimum Fixed Weekday],SUMX(VALUES(Table1[Weekday]),[Employee Minimum Fixed Weekday]))

And finally apply the same approach to the summation at team level:

[Employee Minimum Fixed Team]:=if(HASONEVALUE(Table1[Employee]),[Employee Minimum Fixed User],SUMX(VALUES(Table1[Employee]),[Employee Minimum Fixed User]))

The sample data you provided only shows a couple of dates - one weekday each - and I've assumed the users are both in the same team, so it's hard to illustrate the effect of the different measures, but here's the result (and I trust that it will work with your data):

I would warn, however, that the levels of iteration involved in these calculations will make this slow when working with a large dataset.

Regarding your formatting: if, when you define the measure, you set the number of decimal places, it will apply this formatting wherever the measure is used:

Powerpivot: Sum of minimum values per person per date

1 Answers