How do you calculate ratios in a straight table?

[#1]

Hello,

I'm working a performance report that shows budget vs actuals and variance to prior periods. My issues is that I need to calculate a number of ratios based on the measures presented in the report. I'm able to do the variances quite easy using COLUMN(X) - COLUMN(Y) but the ratios are much more difficult as I can't do ROW(A)/ROW(C).

Is there a clever way to reference fields in the same way you would for Excel? I've posted a sample of the report as well as a few example ratio calculations I need to do.

My thought is that I can use variables to calculate the value every time a list box/filter changes, but I can't quite sort how I could make this show up in every column I have (calculating ratio variances in the same way I'm doing budget vs actual).

I tried pre-calculating the ratios but this won't work because I don't have a set value to weight them against (there are many list boxes in the actual report and the time scale goes from years down to months).

Thanks in advance.

[#2]

Are you trying to calculate those ratios inside the chart or outside?

You can get the expressions you want using Set Analysis and the TOTAL modifier inside Sum. TOTAL means to do the aggregation but ignore the dimension. This what you would use to get the value of a measure on the line of a different measure. This expression will get you the Actual for C regardless of which row you are on:

=SUM(
 {$<Year = {"$(=max(Year))"}, /* Set to the current Year */
 Measure = {"MeasureC"}
 >}
 TOTAL Amount * IsActual
)

If you want this expression outside of the chart, then you don't need the TOTAL. I attached an example.

[#3]

That makes sense as I could do the calc there getting the specific measures I need, but I'm still not sure how I'd get each ratio back in to the structure I already have.

I've updated the example to show the end result I'm hoping to achieve.

Thanks for the feedback.

Jeff

[#4]

You need those ratios to be dimensions and that makes it kind of tricky. You may be able to creatively set up a pivot to make it easier, but I'm not sure.

I was able to get a start on it by making an inline load to add RatioA and RatioB to the Measure field. Then I added if statements to determine which formula to use. I had to set up three different formulas in each expression. It can get very messy.

Take a look at the attachment and see if that will work on your real data.

You add another layer of difficulty by having different types (integer, percentage) in each column. In the past, I was able to cheat and force the percentages to look like percentages by appending a percentage sign and rounding. There may be a function that will handle that part for you.

[#5]

You did put me in the right direction with your suggestions and I've been able to get everything in line. Unfortunately my actual data has 12 ratios that need to be calculated 3 different ways (actual, budget, prior period). To keep it from getting too unwieldy, I want to pass values to variables such as the set or flags to use.

I can get the ratios to calc in Text Boxes but not in a Straight Table, but the moment I add pass values, it breaks in the Straight Table but not the Text Box. I call them both the same way:

=num($(RatioC(IsBudget)),'#,##0.0%')

Any thoughts to what I'm doing wrong? The attached shows RatioC breaking in Table but not the text box.

Thanks again.

[msteedle]

What you're trying to do isn't possible in a single table chart, barring an unfathomable number of nested IF statements. Virtually every cell of the chart (except the first three rows) would have to have a distinctly different formula, not to mention varying data types. Even if this table was a freeform spreadsheet, you would have to type each formula manually, without being able to copy across rows or columns.

The easiest way to get this to work is to duplicate the Performance Report chart for each of the ratios you want to add, then hide the caption and suppress the header row (see Presentation tab) of your new copy and stack them on top of each other. You will also need to hardcode a string of the ratio name as the sole dimension.

[msteedle]

Additionally, you can still make building this easier through the use of variables. Create one variable for each of the three Measures for Current Year Actual and Budget and Prior Year Actual. That's nine total variables to populate all of your other straight tables.