title: Calculating a Ratio using Column A & Column B - in Powerpivot/MDX/DAX, not in SQL
body: <p>I have a query to pull clickthrough for a funnel, where if a user hit a page it records as "1", else NULL --</p>
  
  <pre><code>SELECT datestamp
      ,COUNT(visits) as Visits
      ,count([QE001]) as firstcount 
      ,count([QE002]) as secondcount
      ,count([QE004]) as thirdcount
      ,count([QE006]) as finalcount
      ,user_type
      ,user_loc
  FROM
      dbname.dbo.loggingtable
  GROUP BY user_type, user_loc
  </code></pre>
  
  <p>I want to have a column for each ratio, e.g. firstcount/Visits, secondcount/firstcount, etc. as well as a total (finalcount/Visits).</p>
  
  <p>I know this can be done</p>
  
  <ul>
  <li>in an Excel PivotTable by adding a "calculated field"</li>
  <li>in SQL by grouping</li>
  <li><p>in PowerPivot by adding a CalculatedColumn, e.g. </p>
  
  <pre><code>=IFERROR(QueryName[finalcount]/QueryName[Visits],0)
  </code></pre></li>
  <li><p>BUT I need give the report consumer the option of slicing by just user_type or just user_loc, etc, and excel will tend to ADD the proportions, which won't work b/c</p>
  
  <pre><code>SUM(A/B) != SUM(A)/SUM(B)
  </code></pre></li>
  </ul>
  
  <p><strong>Is there a way in DAX/MDX/PowerPivot to add a calculated column/measure, so that it will be calculated as SUM(finalcount)/SUM(Visits), <em>for any user-defined subset of the data</em> (daterange, user type, location, etc.)?</strong></p>