none
Creating Grouping for PowerPivot Tables

    Question

  • I have a sales table which lists all of my orders and the amount paid for each order. I know want to create a PowerPivot table that groups all my orders into select order price boundaries e.g.

    0 - £100

    101 - £200

    201 - £500

    ... and so on.

    I can do this by using a calculated column and doing a long IF statement but I know this is less than ideal, can someone recommend another way?

    I thought of setting up a separate table in the data model containing just boundaries but could not think of approach that would actually work.

    Thanks in advance.
     

    Monday, September 30, 2013 7:51 PM

Answers

  • You can create a separate "Range" table with min and max boundaries and the label for a given range.  And then create a calculated column in your sales table that references the range table.

    Something like this.

    =CALCULATE(VALUES(Ranges[RangeName])
                                ,FILTER(Ranges,Sales[amount] >= Ranges[min] && Sales[amount] <= Ranges[max])
                             )

    Alberto Ferrari has a nice post explaining this in more detail here if you're interested. 

    Let me know if that helps.


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


    Tuesday, October 01, 2013 3:54 AM
    Answerer

All replies

  • You can create a separate "Range" table with min and max boundaries and the label for a given range.  And then create a calculated column in your sales table that references the range table.

    Something like this.

    =CALCULATE(VALUES(Ranges[RangeName])
                                ,FILTER(Ranges,Sales[amount] >= Ranges[min] && Sales[amount] <= Ranges[max])
                             )

    Alberto Ferrari has a nice post explaining this in more detail here if you're interested. 

    Let me know if that helps.


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


    Tuesday, October 01, 2013 3:54 AM
    Answerer
  • That's perfect, thanks very much for this.

    Monday, October 07, 2013 6:54 AM