locked
Summarize a summarized Table RRS feed

  • Question

  • Is it somehow possible to summarize a summarized table? I can't make it work with summarize(summarize(...)...)

    e.g. I have a table with columns: user, day, value, (month)

    => summarize this by user, month and calculate the value per month

    Now I want to find the maximum value per month per user. I can easily calculate this with maxx.

    But in the end im interested in the count of the max value per user per month which is e.g. >x. From my understanding i would need to summarize the table user, month, value per month and calculate a new column with maxx(value per month)...

    At the moment i solve the problem by having a pivot table in excel with user, maxx(value per month) and than counting the values >x.

    Hope you understand what i mean. Still pretty new to Powerpivot/DAX.

    Thanks

    Monday, June 25, 2012 8:22 AM

Answers

  • Just had a similar challenge, and I ended up solving it in DAX by using the SUM(CASE) conditional counting technique that is quite powerful in SQL.  Essentially you use SUMMARIZE as you've mentioned to roll the data up to the month level to get your MAX per user per month, and make a comparison to the "x" value you are testing against.  Then wrap a SUMX around that summary table and sum a counter that is populated by an IF function that tests if the Difference between the 2 values is > 0 (IF true, 1, else 0).  Could also wrap the expression in the summarized table and just return 1's and 0's. 

    Looks like this:

    =SUMX(
          SUMMARIZE(Table1
                    ,Table1[user]
                    ,Table1[month]
                    ,"Var"
                    ,MAX[Value] - << your "x" expression here>>
                    )
          ,IF([Var] > 0 , 1 , 0)
          )
    Hope that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, June 25, 2012 2:31 PM
    Answerer