Monday, February 13, 2012 3:00 PM
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:
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?
Monday, February 13, 2012 3:41 PM
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?
Monday, February 13, 2012 3:57 PM
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...
Monday, February 13, 2012 4:44 PM
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?
Monday, February 13, 2012 5:54 PM
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
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!
Monday, February 13, 2012 6:35 PM
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]) )
- Marked As Answer by Challen FuModerator Thursday, February 16, 2012 6:15 AM
Monday, February 13, 2012 9:00 PMI was hoping to publish to SharePoint and heard v2 isn't compatible yet, so I was holding off on upgrading...
Monday, February 13, 2012 9:27 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]))
Friday, February 17, 2012 3:49 AM
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
Friday, February 17, 2012 4:42 PM
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
- Marked As Answer by thojo0513 Friday, February 17, 2012 9:42 PM
Friday, February 17, 2012 9:43 PM
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!
Tuesday, April 10, 2012 11:40 AM
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