How to Add Correct Grand Total for Rows

[pjmorris]

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

[/TD]
[TD]Col2

[/TD]
[TD]Col3

[/TD]
[TD]Col4

[/TD]
[TD]GrandTotal
[/TD]
[/TR]
[TR]
[TD]Row1
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]MeasureTotal
[/TD]
[/TR]
[TR]
[TD]Row2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]MeasureTotal
[/TD]
[/TR]
[TR]
[TD]Row3
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]MeasureTotal
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I know how to create measures and the Row/Col entries on my PivotTable are correct. But the Grand Total is not - I assume that the solution to obtaining the number will be the correct use of CALCULATE, which I'm investigating separately. My question is that having correctly formed the measure how do I add it to the PivotTable so that it is a Grand Total for a ROW (not Column) and hence only appears once and not for every Column of which there are several.

The outcome I'm seeking is shown above. What I get are multiple copies of the 'MeasureTotal' against each column.

I feel this is either something simple, or impossible. If you can tell me which and how if the former it will help enormously.

Many thanks.

Peter

 

[VBA Geek]

generally You can use the HASONEVALUE function to determine which measure to call

IF ( HASONEVALUE ( CategoryOnColumns ), [measure for cells within pivot table], [measure for grand total] )

there are other ways to do it depending on the calculations you are doing

 

[pjmorris]

Hi, I'm sure I'm missing something easy, but however I define the measure how do I make it appear only once for each row despite there being multiple columns?

 

[VBA Geek]

Could you share an example?

 

[pjmorris]

Hi, Apologies for the delay in replying - but its take me this long to create some sample data and feel confident that I've asked the right question.

Background: I will have two tables. the first will contain a list of tasks (rows) and all possible resource groups (columns - called Booking Groups). For each booking group I have a certain amount of available resource (hours) which I want to assign to the tasks based on their expected effort (a percentage). The issue described above is that while I made individual entries work I couldn't get the Grand Total Rows to work. I've been going mad trying to identify the cause). I think the following comments now describe at least part of the problem.

Data Source. This is a small sample of how the task table might look which is how users like to enter their assignments:

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

​

[/TH]
[TH]

​

[/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Data Table of Tasks against Booking Groups[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD][/TD]
[TD]Booking Groups[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #4472C4"]Task[/TD]
[TD="bgcolor: #4472C4"] A [/TD]
[TD="bgcolor: #4472C4"] C [/TD]
[TD="bgcolor: #4472C4"] D [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #D9E1F2"]Task T1[/TD]
[TD="bgcolor: #D9E1F2"]

20%​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task T2[/TD]
[TD][/TD]
[TD]

5%​

[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #D9E1F2"]Task G3[/TD]
[TD="bgcolor: #D9E1F2"]

80%​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task G4[/TD]
[TD][/TD]
[TD]

11%​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #D9E1F2"]Task Something[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

84%​

[/TD]
[TD="bgcolor: #D9E1F2"]

60%​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


When 'Unpivoted' using PowerQuery this becomes:

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

​

[/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #70AD47"]TASK[/TD]
[TD="bgcolor: #70AD47"]Booking Group[/TD]
[TD="bgcolor: #70AD47"]Expected Effort[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #E2EFDA"]Task T1[/TD]
[TD="bgcolor: #E2EFDA"]

A​

[/TD]
[TD="bgcolor: #E2EFDA"]

20%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task T1[/TD]
[TD]

D​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #E2EFDA"]Task T2[/TD]
[TD="bgcolor: #E2EFDA"]

C​

[/TD]
[TD="bgcolor: #E2EFDA"]

5%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task T2[/TD]
[TD]

D​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #E2EFDA"]Task G3[/TD]
[TD="bgcolor: #E2EFDA"]

A​

[/TD]
[TD="bgcolor: #E2EFDA"]

80%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task G3[/TD]
[TD]

D​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Task G4[/TD]
[TD="bgcolor: #E2EFDA"]

C​

[/TD]
[TD="bgcolor: #E2EFDA"]

11%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task G4[/TD]
[TD]

D​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #E2EFDA"]Task Something[/TD]
[TD="bgcolor: #E2EFDA"]

C​

[/TD]
[TD="bgcolor: #E2EFDA"]

84%​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task Something[/TD]
[TD]

D​

[/TD]
[TD]

60%​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


Then, using PowerPivot, I created the following table. One set of column entries is an implicit calculation 'Sum of Expected Effort' the other is a measure defined as 'SummedEffort'=sum('ExpectedEffort by Booking Group'[Expected Effort]) The two results are clearly different when I expected them to be the same (I'm reading Rob Collie's book on PowerBI etc which discusses implicit and explicit functions). The correct entry I believe are the ones in the 'Sum of Expected Effort' columns. I think it is this difference which is causing my problems as I need to multiply SummedEffort by the available resource - if SummedEffort were the same as the other column all would be well in the world.


Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

​

[/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

Column Labels​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

A​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

C​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

D​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"]

Total Sum of Expected Effort​

[/TD]
[TD="bgcolor: #D9E1F2"]

Total SummedEffort​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #D9E1F2"]Row Labels[/TD]
[TD="bgcolor: #D9E1F2"]

Sum of Expected Effort​

[/TD]
[TD="bgcolor: #D9E1F2"]

SummedEffort​

[/TD]
[TD="bgcolor: #D9E1F2"]

Sum of Expected Effort​

[/TD]
[TD="bgcolor: #D9E1F2"]

SummedEffort​

[/TD]
[TD="bgcolor: #D9E1F2"]

Sum of Expected Effort​

[/TD]
[TD="bgcolor: #D9E1F2"]

SummedEffort​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task G3[/TD]
[TD]

80%​

[/TD]
[TD]

300%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

10%​

[/TD]
[TD]

300%​

[/TD]
[TD]

90%​

[/TD]
[TD]

300%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task G4[/TD]
[TD][/TD]
[TD][/TD]
[TD]

11%​

[/TD]
[TD]

300%​

[/TD]
[TD]

10%​

[/TD]
[TD]

300%​

[/TD]
[TD]

21%​

[/TD]
[TD]

300%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task Something[/TD]
[TD][/TD]
[TD][/TD]
[TD]

84%​

[/TD]
[TD]

300%​

[/TD]
[TD]

60%​

[/TD]
[TD]

300%​

[/TD]
[TD]

144%​

[/TD]
[TD]

300%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task T1[/TD]
[TD]

20%​

[/TD]
[TD]

300%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

10%​

[/TD]
[TD]

300%​

[/TD]
[TD]

30%​

[/TD]
[TD]

300%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]Task T2[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5%​

[/TD]
[TD]

300%​

[/TD]
[TD]

10%​

[/TD]
[TD]

300%​

[/TD]
[TD]

15%​

[/TD]
[TD]

300%​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD="bgcolor: #D9E1F2"]Grand Total[/TD]
[TD="bgcolor: #D9E1F2"]

100%​

[/TD]
[TD="bgcolor: #D9E1F2"]

300%​

[/TD]
[TD="bgcolor: #D9E1F2"]

100%​

[/TD]
[TD="bgcolor: #D9E1F2"]

300%​

[/TD]
[TD="bgcolor: #D9E1F2"]

100%​

[/TD]
[TD="bgcolor: #D9E1F2"]

300%​

[/TD]
[TD="bgcolor: #D9E1F2"]

300%​

[/TD]
[TD="bgcolor: #D9E1F2"]

300%​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


I suspect the answer lies in the correct use of 'Calculate(...)', but I don't seem to be able to get it right. Grateful for any advice or guidance.

Regards

Peter

 

[VBA Geek]

It looks like a relationship problem because you are getting the table total when using your measure, with the relationship correctly set up I am getting the right numbers.
Also be careful on your un-pivot steps, the group D ( 10 % ) is getting assigned to tasks that have Group D blank in the original table