Incorrect GrandTotals

[pjmorrisParticipant Started: 2Replies: 2Total: 4]

Please help!

I have two tables: the first is a list of tasks which contain task[name], task[booking group] and task[effort].  The ‘effort’ field has percentages of effort required from the resource named in ‘booking group’.  The second table has resources[booking group] and resources[hours] (this is a connection to a PowerQuery that groups information from a bigger table)).  The two tables are linked via the [booking group] field.

 

I want to calculate a measure: hoursbytask=[SumofEffort] * [SumofHours], where:

SumofEffort=sum(task[name]) and

SumofHours=sum[resources[hours])

which works when booking group is shown as the column headings and tasks are shown as the rows (and vice versa of course).  However the Grand Totals are incorrect because the grand total sums all the resource tables hours (because no filter is applied) and multiplies this by [SumofEffort].  I suspect that the answer is a correctly structured CALCULATE, but I’m at a loss to tell the system to calculate something which I think might look like this if written in full:

hoursbytask=CALCULATE([SumofEffort] * [SumofHours], resources[booking group]=group1) + CALCULATE([SumofEffort] * [SumofHours], resources[booking group]=group2) + CALCULATE([SumofEffort] * [SumofHours], resources[booking group]=group3) etc

Grateful for any advice and guidance.

Regards

Peter

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Peter,

The following measure is listed:

SumofEffort=sum(task[name])

Should it be listed as:

SumofEffort=sum(task[effort]) ?

Since task efforts contains a percentage of a resource, I assume that the relationship is that one resource has many tasks, which also implies that the resources are booking groups. Am I correct in these assumptions?

It is also mentioned that booking group is used for the pivot’s column headers, but is that the same booking group column used in the relationship? If so, are you using the booking group column from the lookup table or from the detail table? I am assuming that you are using the booking group column from the parent table (Resources, based on previous paragraph).

If all of my assumptions are correct, your measure that combines resources and tasks should look more like:

SUMX ( Tasks, Tasks[Effort] * RELATED ( Resource[Hours] ) )

[pjmorrisParticipant Started: 2Replies: 2Total: 4]

Hi Tom,

Firstly, your assumptions are all spot-on and apologies for the error in SumofEffort.

Secondly, your answer works perfectly for which I’m really grateful – I have been struggling with this for weeks and have been learning Powerpivot at the same time! Its a really useful tool, but quite a different way of thinking from my previous approach without the ‘Power’ tools.

Again, many thanks – the help is very much appreciated.

Regards

Peter

[Author]

Posts