# Ratio Analysis

• ### Question

• I need to calculate a few ratios.  For example lets say that my data includes revenue (sales price) and profit (sales price - cost) .  I want to calculate margin (Profit/revenue).  In the "Power Pivot for Excel" window, I defined a new column (e.g. =[Proft]/[Revenue]) and format it as a percentage.  When I drop that into a pivot table, I get the following.  It is summing my percentages and formatting them as currency.

 Quarter Profit Revenue Margin Q1 \$12,898,480.33 \$34,049,472.69 \$42,285.49 Q2 \$11,972,452.13 \$32,885,474.52 \$42,752.49 Q3 \$10,963,264.86 \$28,855,438.87 \$39,595.47 Q4 \$10,561,698.78 \$29,413,903.70 \$38,958.75 Grand Total \$46,395,896.11 \$125,204,289.78 \$163,592.19

I want to get something like this.  Obviously I am new to PowerPivot.  I do not want to aggregate the individual margins, but rather, I want to calculate the ratio based on the aggregated Profit and revenue.  I believe that I need to use the CALCULATE function, but I cannot find much information.  Can alyone help me?

 Quarter Profit Revenue Margin Q1 \$12,898,480 \$34,049,473 37.88% Q2 \$11,972,452 \$32,885,475 36.41% Q3 \$10,963,265 \$28,855,439 37.99% Q4 \$10,561,699 \$29,413,904 35.91% Total \$46,395,896 \$125,204,290 37.06%
Monday, June 28, 2010 3:01 PM