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

  • 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 PM
    Answerer
  • Joe,

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

    Image and video hosting by TinyPic

    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/

    Friday, February 17, 2012 4:42 PM
    Answerer

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 PM
    Answerer
  • 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 PM
    Answerer
  • 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

    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 PM
    Answerer
  • 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 PM
    Answerer
  • 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

    Friday, February 17, 2012 3:49 AM
  • Joe,

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

    Image and video hosting by TinyPic

    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/

    Friday, February 17, 2012 4:42 PM
    Answerer
  • 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