Ratio Analysis RRS feed

  • 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


  • Never mind.  I understand it now.  I was attempting to create the measure in the "Power Pivot for Excel" window rather than the main excel window.  If anyone is interested, you create a measure using the measure button on the PowerPivot ribbon of the main window.

    Monday, June 28, 2010 6:27 PM