Answered by:
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
Monday, February 13, 2012 3:00 PM
Answers

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/ Marked as answer by Challen Fu Thursday, February 16, 2012 6:15 AM
Monday, February 13, 2012 6:35 PMAnswerer 
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/ Marked as answer by Joe Thompson MCSE Friday, February 17, 2012 9:42 PM
Friday, February 17, 2012 4:42 PMAnswerer
All replies

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/Monday, February 13, 2012 3:41 PMAnswerer 
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
Monday, February 13, 2012 3:57 PM 
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/Monday, February 13, 2012 4:44 PMAnswerer 
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, 01012012, host1, vmactive, 4
2012, jan, 01022012, host1, vmactive, 4
2012, jan, 01032012, host1, vmactive, 4
2012, jan, 01012012, host2, vmactive, 3
2012, jan, 01022012, host3, vmactive, 5pivot 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
Monday, February 13, 2012 5:54 PM 
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/ Marked as answer by Challen Fu Thursday, February 16, 2012 6:15 AM
Monday, February 13, 2012 6:35 PMAnswerer 
I was hoping to publish to SharePoint and heard v2 isn't compatible yet, so I was holding off on upgrading...
Joe Thompson
Monday, February 13, 2012 9:00 PM 
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/ Marked as answer by Challen Fu Thursday, February 16, 2012 6:15 AM
 Unmarked as answer by Joe Thompson MCSE Friday, February 17, 2012 3:44 AM
Monday, February 13, 2012 9:27 PMAnswerer 
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 14Joe Thompson
Friday, February 17, 2012 3:49 AM 
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/ Marked as answer by Joe Thompson MCSE Friday, February 17, 2012 9:42 PM
Friday, February 17, 2012 4:42 PMAnswerer 
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
Friday, February 17, 2012 9:43 PM 
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
Tuesday, April 10, 2012 11:40 AM