locked
count how many ordernos that have a total greater than 200 RRS feed

  • Question

  • I would like to count how many ordernos that have a total greater than 200 (aaa has a total of 300 below)

     

    orderno amount

    aaa 100

    aaa 100

    aaa 100

    bbb 100

    bbb 100

     

    Thursday, March 10, 2011 5:27 PM

Answers

  • Create a new column in your PowerPivot fact table and use this function.  This will create a formula which will aggregate to 1 for each order that is greater than 200, and provide appropritate totals. 

    =IF(CALCULATE(SUMx('table1', 'Table1'[amount]),ALLEXCEPT('table1','table1'[orderno]))>200,1/CALCULATE(COUNTROWS('table1'), ALLEXCEPT('Table1', 'Table1'[Orderno])),0)


    Thanks, Barbara
    • Proposed as answer by Barbara Raney Thursday, March 10, 2011 6:35 PM
    • Marked as answer by d_ddd Saturday, March 12, 2011 9:56 AM
    Thursday, March 10, 2011 6:35 PM

All replies

  • Hi d_ddd

    One way of doing this would be to first create another table to hold the orders, and then adding a conditional formula to only count those orders with a total amount of more than 200.

    Once your table is in PowerPivot (Table1), create a pivot table and select only the orderno in the 'row labels' section of the field list. right click on the pivot table, go to 'pivot table options', 'totals & filters' and uncheck the 'select grand total for columns' option. 

    Then, convert the pivot table to formulas (Pivot Table tools in the ribbon -> OLAP tools -> Convert to Formulas) in order to re-import this list of distinct orders as a new table in PowerPivot (Table2)

    Now establish a relationship between both tables using the 'orderno' column, and having the second table as a lookup.

    You can now use create a calculated column in the second table, using this DAX formula:


    =IF( CALCULATE( SUM(Table1[amount]),   RELATEDTABLE(Table1)  ) > 200, 1, 0)

     This is assign a 1 only to those orders will a a total amount of more than 200, so only those will be counted. You can then pivot Table2, and you should see which orders make it into the total (in your case, it will be orderno 'aaa')

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, March 10, 2011 6:20 PM
    Answerer
  • Create a new column in your PowerPivot fact table and use this function.  This will create a formula which will aggregate to 1 for each order that is greater than 200, and provide appropritate totals. 

    =IF(CALCULATE(SUMx('table1', 'Table1'[amount]),ALLEXCEPT('table1','table1'[orderno]))>200,1/CALCULATE(COUNTROWS('table1'), ALLEXCEPT('Table1', 'Table1'[Orderno])),0)


    Thanks, Barbara
    • Proposed as answer by Barbara Raney Thursday, March 10, 2011 6:35 PM
    • Marked as answer by d_ddd Saturday, March 12, 2011 9:56 AM
    Thursday, March 10, 2011 6:35 PM