locked
Calculating Sum fo Squares over a period RRS feed

  • Question

  • I 've created a PivotTable with CalendarYear and EnglishMonthName in the Row area and Sum of Sales Amount in the Values area. I created a calculated measure [SalesSq]=[Sum of Sales]^2, which gives me the correct values for individual months. However, I need to calculate the sum of the individual month squares over each year (or any other specified period for that matter). All my attempts so far has yielded the incorrect results for sum of month squares over the year. I end up either with the square of total sum of sales over the year, or the sum of squares of each individual day totaled for each month (not the sum of the month squared), which again leads to the incorrect sum of squares for the year.
    Friday, January 22, 2010 6:07 PM

Answers

  • Thank you very much Colin - the column for a different base period is also interesting.

    Marco
    Marco Russo
    • Marked as answer by Mardin Pena Friday, March 5, 2010 8:50 PM
    Monday, January 25, 2010 1:31 AM

All replies

  • Hi, Colin,

    I consulted Marius on this. He suggested the following solution. I tested it out and it appeared working. Please let us know if this is what you are looking for.

    TotalSales
    =Sum('FactAllSAles'[SalesAmount])

    SquareOfMonthSales
    =SUMX( VALUES(DimDate[EnglishMonthName]), 'FactAllSales'[TotalSales] * 'FactAllSales'[TotalSales] )

    And here is the sample result I got:

    Row Labels Sum of SalesAmount TotalSales SquareOfMonthSales
    2003 1803.8435 1803.8435 $679,837.87
    July 267.902 267.902 $71,771.48
    August 556.0315 556.0315 $309,171.03
    September 44.95 44.95 $2,020.50
    October 366.792 366.792 $134,536.37
    November 305.66 305.66 $93,428.04
    December 262.508 262.508 $68,910.45
    2004 4935.994 4935.994 $3,361,442.89
    January 176.204 176.204 $31,047.85
    February 239.134 239.134 $57,185.07
    March 161.82 161.82 $26,185.71
    April 233.74 233.74 $54,634.39
    May 884.616 884.616 $782,545.47
    June 269.7 269.7 $72,738.09
    July 44.95 44.95 $2,020.50
    August 1123.75 1123.75 $1,262,814.06
    September 377.58 377.58 $142,566.66
    October 80.91 80.91 $6,546.43
    November 570.45 570.45 $325,413.20
    December 773.14 773.14 $597,745.46

    Thanks,
    Lisa
    • Proposed as answer by Marco Russo Sunday, January 24, 2010 10:26 PM
    Friday, January 22, 2010 10:10 PM
  • Thanks Lisa, that works! Using 'FactAllSales'[TotalSales]^2 appears to work equally well. 
    Friday, January 22, 2010 11:01 PM
  • Colin,

    I've seen the problem and the solution and it is an interesting example of the scenario where SUMX( VALUES(...), ... ) is required.
    I just have a question for you: is there a (business/analysis) reason for which you needed to create the square of sales value? Is it part of a formula (i.e. an indicator, standard deviation, ...) or is there a specific need for this calculation?
    Thanks!

    Marco
    Marco Russo
    Sunday, January 24, 2010 10:30 PM
  • Marco,

    Yes, it's part of a standard deviation calculation for establishing 6-sigma limits on a data set. With Year and Month in the PivotTable, I have to remove the month filter (ALL(month)) so that the measure shows the same yearly deviation for each month of the year (and thus a horizontal line on a chart). Interestingly, I've needed to calculate the SDev over periods of more than one year. To do that, I've had to create a calculated field for the period I need (say 18-months or two years) and use this period field in the PivotTable Row area instead of Year.

    Colin
    Sunday, January 24, 2010 11:30 PM
  • Thank you very much Colin - the column for a different base period is also interesting.

    Marco
    Marco Russo
    • Marked as answer by Mardin Pena Friday, March 5, 2010 8:50 PM
    Monday, January 25, 2010 1:31 AM