how to sum a subtotal of a calculated measure or column that comes from a measure that is averaged

Question

I am trying to sum up the subtotal of a measure that comes aggregrated as an average.

I have imported an OLAP cube that has totals alread aggregated on a daily basis as an averate. so I have 30 days of data, which each day having an aggregated average. I am unable to figure out how to pull that back to a pivot table and summarize by SUM, its grayed out and will only show the average.

Data looks like this

Year, Month, Day, DisplayName, Counter, Average, Max

The formula that I am currently using for the calculated measure is:

CALCULATE( SUM(VMHostCntSUM[MeasuresAverage])/(COUNTROWS(VMHostCntSUM)),VMHostCntSUM[CounterName]="vmActive")

It pulls back the correct values when I apply the filter to the pivot table, but instead of suming the values, it averages them. Any ideas?

Thank you!

---

Joe Thompson

Answer 1

Hi Joe,

Your DAX expression is explicitly doing an average by specifiying a SUM over a COUNT.  (countrows in this case).  If you just want to sum, couldn't you just remove the denominator?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

The problem with just a straight Sum measure is that it SUMS the averages from the table. So if the average value per day is 4 over the course of 30 days, it will give you a value of 120, instead of the average, which is 4. BUT, I'm trying to rollup the value to host servers, and if the measure is an average, then the subtotal in the pivot table is an average, when I need it to be a SUM of the averages.

This formula below pulls back the correct value, but in the pivot table, it is summarized by Average, instead of SUM. I am just starting out with PowerPivot, so am still learning...

=AVERAGEX(FILTER(VMHostCntSUM,VMHostCntSUM[CounterName]="vmActive"),VMHostCntSUM[MeasuresAverage])

---

Joe Thompson

Answer 3

Hi Joe

Just to be sure I am able to clearly understand what you need, can you post a small sample of your data and the expected output?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 4

Cluster is related to host1, host2, host3 - cluster should be a rollup SUM of the host averages

Host powerpivot table

Year, Mth, date, displayname, counter, avg

2012, jan, 01-01-2012, host1, vmactive, 4
2012, jan, 01-02-2012, host1, vmactive, 4
2012, jan, 01-03-2012, host1, vmactive, 4
2012, jan, 01-01-2012, host2, vmactive, 3
2012, jan, 01-02-2012, host3, vmactive, 5

pivot table

cluster1 vmactive 12    <---- I need the sum like this, right now, I can only get the average of the average

       host1 vmactive 4

       host2, vmactive 3

       host3, vmactive 5

Hopefully that makes sense, thank you for your help!

---

Joe Thompson

Answer 5

Joe,

Got it.  What you need is to execute the aggregate at two levels, first average and then sum.

Are you using PowerPivot 2? (Currently in RC0)

If so, you can use the SUMMARIZE function as in the expression below (assume the table above is called [Table2]):

IF(COUNTROWS(VALUES(Table2[displayname]))>1,
	SUMX( 
		SUMMARIZE(
			Table2, 
			Table2[displayname], 
			Table2[counter], 
			"AvgMeasure", AVERAGE(Table2[avg])
		), 
	[AvgMeasure])
	,
	AVERAGE(Table2[avg])	
)

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 6

I was hoping to publish to SharePoint and heard v2 isn't compatible yet, so I was holding off on upgrading...

---

Joe Thompson

Answer 7

In PowerPivot version 1, you can follow this approach:

Create a calculated column called "host - counter" with the following expression:

Table1[displayname] & "," & Table1[counter]

This column gives the proper granularity for the first level average.  Then create a measure:

SUMX(VALUES(Table1[host - counter]), AVERAGE(Table1[avg]))

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 8

I appoligize for the delay in responding to your answer, but I have tested it and it returns double the value expected.

Cluster  Value expected  Value Returned
 - Host 1  4                     8
 - Host 2  8                     16
 - Host 3  7                     14

---

Joe Thompson

Answer 9

Joe,

Following the solution I outlined with the data you provided, I get this:

The resulting measure has the correct average for each 'host', and the grand total is a sum of averages.   My understanding is this is what you needed.

In your last response you say the average for host 2 should be 8, when in the source data you posted i only have one value host 2 (the value is 3).  How do you get an expected average output of 8 with the data provided?  I'm confused

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 10

Javier,

You were spot on with your solution, I was trying to pull back multiple counters in one lookup table which was throwing off my results. I have limited the powerpivot table query to only pull back a single counter per table and your solution worked like a champ. Thank you for all your help!

---

Joe Thompson

Answer 11

Javier,

May be you can help.

This SUMMARIZE formula drives me crazy. I doesn't get displayed more than the Measure name and "12". No table as defined. No columns, rows at all. The same with the codeplex sample data and MSDN Tech formula, which is shown in the help section.

Verifying the formula above PowerPivot V2 shows "no errors found", but keeps everything empty beyond the "12". Applying the MSDN Tech help section formula nothing but errors like columns not found, tables / columns returned instead of scalar values. I tried all variations with countrows, values, related.

Is it possible, that the German Office language pack is "disturbing" the V2 of PowerPivot? (SUMMARIZE variables are greyed out completely during inputs)

Thanks in advance

Thomas

---

Thomas