This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
I have a table with order 3 million rows that has 6 values for the source of a sales order(C, M, W, P, I, Blank). I would like to count the number of rows with the value of C, M, W, P as “total 1” and count the other rows as “total 2”. Then show “total 1” / all rows and “total 2” / all rows. How would I do that?
John,
Please see attached workbook; it is one way to reach your objective.
I replaced the blanks in the data table source code column with an identifier (“O”) representing other and then created a dimension table for Order Source codes and their name. Measures were written as follows:
Total Rows:=COUNTROWS ( Orders )
All Rows:=CALCULATE ( [Total Rows], ALL ( Orders ) )
Total 1:=CALCULATE ( [Total Rows], FILTER ( ALL ( Sources ), [SrcCode] = “W” || [SrcCode] = “C” || [SrcCode] = “M” || [SrcCode] = “P” ) )
Total 2:=CALCULATE ( [Total Rows], FILTER ( ALL ( Sources ), [SrcCode] = “I” || [SrcCode] = “O” ) )
Total 1 as % of All Rows:=DIVIDE ( [Total 1], [All Rows] )
Total 2 as % of All Rows:=DIVIDE ( [Total 2], [All Rows] )Hope this provided some ideas for you.
Thank you – from your response I was able to perform the calculations I needed. I’ve been working with Power Pivot for couple months and it is awesome!
The only feature I miss with Power Pivot is the ability to have parameters when doing the data extract from our SQL server. In the past I would have the users enter a value in a cell (or cells), and then use them as parameters in the SQL statement to minimize the amount of data being extracted.
John,
I have seen the parameter approach you describe when using Power Query to bring data into Power Pivot. For one example, check out the following url:
http://www.powerpivotblog.nl/automatically-ranged-date-table-using-dax-and-m/
The forum ‘Power Pivot’ is closed to new topics and replies.