Answered by:
count how many ordernos that have a total greater than 200
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 reimport 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.aspxThursday, March 10, 2011 6:20 PMAnswerer 
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