Sum of Calculated Measure not Working

[knotty150]

Morning All
I’m having a very annoying issue with a calculated measure not summing up correctly in a PivotTable. The calculated measure is attempting a weighted average; it does everything correctly at the row below level, but sums a field it makes reference to in the formula, as opposed to summing the formula itself. Can anyone provide a solution/explanation without me going into the detail of the dataset/formula? Obviously I’ve got it all to hand if need be.
Thanks in advance!
Rich

 

[scottsen]

The general pattern for "my rows don't add up the way I expect is":
=SUMX(MyTable, [MyExpression])

But... that obviously won't make sense at a grand total for weight average, and really... neither will =AVERAGEX giving the average of your weighted averages.

I think I will need to see your measures, sample data and such...

 

[knotty150]

Hi Scottsen, thanks for your reply. I’ll outline what’s going on, highlighting in bold where I’ve used calculated measures:
I’m having some trouble with a weighted average calculation I’m trying to achieve. Assuming I have a table of vehicle leasing agreements sales, I’ll provide an example dataset:

Salesperson	Cost	Life (months)
Roger	500	36
Roger	800	48
Roger	2000	48
Roger	750	36
Keith	3000	60
Keith	1500	36
Keith	2100	48
Roger	600	36
Keith	500	48
Keith	900	60

<tbody>
</tbody>

I’m trying to calculate an average of the “Life (months)" field, weighted by the value of the “Cost” field, and express that by “Salesperson.” The steps I'm using to calculate this are as follows:
1.Sum the “Cost” field by “Salesperson” MyNumber:=SUM(Cost):

Roger = 4650
Keith = 8000

2.Divide each record of “Cost” by the corresponding “Sum of Cost by Salesperson” MyPercentage:=[MyNumber]/CALCULATE([MyNumber],ALL([MyNumber])):” e.g. Roger:

Salesperson	Cost	Life (months)	MyPercentage
Roger	500	36	0.107527
Roger	800	48	0.172043
Roger	2000	48	0.430108
Roger	750	36	0.161290
Roger	600	36	0.129032

<tbody>
</tbody>

3.Multiply the “Life (months)” field by the “MyPercentage” measure ThisWontSum:=SUM([Life (months)])*[MyPercentage]:

Salesperson	Cost	Life (months)	MyPercentage	ThisWontSum
Roger	500	36	0.107527	3.87
Roger	800	48	0.172043	8.26
Roger	2000	48	0.430108	20.65
Roger	750	36	0.161290	5.81
Roger	600	36	0.129032	4.65

<tbody>
</tbody>

4.Finally, sum the “ThisWontSum" measure:

Correct Answer: Roger = 43.24

I’ve gotten right up to completing step 3 but the final problem is that the sum of the “ThisWontSum" measure is actually calculating the sum of “Life (months)” field (displaying 204 when it should be 43.24). When you create a PivotTable with Salesperson & Cost as ROWS and ThisWontSum as VALUES, ThisWontSum is calculating the individual rows perfectly – it’s just the Grand Total causing me grief.

Sorry if that’s a bit expansive; I’ve just tried to break it all down as clearly as possible. P.S. I’ve thrown “Keith” into the data set merely to demonstrate that there is >1 salesperson.

Thanks in advance for your help scottsen!

Rich

 

[scottsen]

I'm heading to the airport to train some folks... so don't have much time.

But in general, the pattern I gave... is likely how you solve this.

=SUMX(MyTable, [ThisWontSum])


A few other things you can think about:
* It's possibly helpful to have a separate Salesperson table. You can then SUMX(Salepeople, ...) etc
* You can always do a IF (HASONEVALUE(Table[SalesPerson]), DoYourNormalStuff, DoGrandTotalSpecificStuff) if all else fails.

 

[knotty150]

Hi Scottsen

Thanks for your help again. This is where I show how little I know about PowerPivot:

1. =SUMX(MyTable, [ThisWontSum])

I'm getting error messages trying to create that as either a calculated measure or column.

2. I already use a separate table for Salesperson. I'm unsure as to how I can use that to assist in this particular calculation.

3. I'm unfamiliar with "IF (HASONEVALUE(Table[SalesPerson]), DoYourNormalStuff, DoGrandTotalSpecificStuff)." This is my first time using PowerPivot so I don't really do any "NormalStuff" . Would you be able to explain this a bit more please?

Thanks again scottsen, your help is much appreciated!

Rich

 

[XLBob]

find attached from below. I am using Excel 2013, hope you got the same version. I created a caluculated column in the facts table to achieve your step 1 to 3. then use IF(HASONEVALUE(SalesPerson[Salesperson]),SUM(Facts[Cal]),BLANK()) to remove grand total row.
https://onedrive.live.com/?cid=7DF5E31C189063EC&id=7DF5E31C189063EC%21124

 

[knotty150]

Wow, thanks XLBob that looks to be a massive help!

I'll have a crack at applying it to my real data set, will let you know how I get on.

Thanks again, much appreciated!

Rich

 

[knotty150]

Hi Again

I've had chance to have a play with your solution XLBob and I've got it working quite nicely - thanks!

I am however having an issue with adding a timeline to the pivot table/chart. Though I've not included any date field in my sample data set above, I'm aiming to be able to demonstrate monthly trends by salesmen in their weighted average performance. It seems to calculate perfectly fine for the entirety of the time period specified in the query I've written to import the data, but adding a timeline and selecting individual months seems to throw the results out.

Any idea how I can get around this?

Thanks in advance!

 

[XLBob]

I would request you to provide me with a sample workbook. hard to understand exactly what you are trying to do without having a look at your data model

 

[knotty150]

Morning XLBob

Please find sample workbook here:

https://onedrive.live.com/?cid=F9402ACBDF9DC6BE&id=F9402ACBDF9DC6BE%21105

I've included your calculation but as you can see, when you apply a date filter using the slicer, the results skew. What's happening is: the Sum of Cost is calculating for the entire data set (not recalculating based on the date filter applied), thus returning a lower than expected weighted average result. Hope that makes sense?

Thanks for you help, is much appreciated!

Rich