
1. Re: Computing aggregate ratio
Benjamin Greene Oct 27, 2016 6:31 AM (in response to Adil Al Raeesi)1 of 1 people found this helpfulIn your Measure Values card on this sheet, what kind of aggregation does it say it is performing on Div Ohead and YTD Fees? I would assume it is performing a SUM for both of these fields. If that is true, try changing your formula to SUM([Div Ohead])/SUM([YTD Fees]). If either of those fields are being aggregated some other way in your view, then you will want to make sure the calculated field is performing that same aggregation.

2. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 6:41 AM (in response to Benjamin Greene)Hi Benjamin,
The aggregation that it is performing is a SUM for both fields, as you have correctly pointed out.
I tried adjusting my calculated field to say SUM([Div Ohead])/SUM([YTD Fees]); it is returning the same values as in the screenshot above. The only difference is that this calculated field is now being aggregated as 'AGG' under the Measure Values Card instead of a 'Sum' aggregation.

3. Re: Computing aggregate ratio
Benjamin Greene Oct 27, 2016 7:58 AM (in response to Adil Al Raeesi)Hmm that is strange. Here is what I would do if I were trying to diagnose this problem. First, I would change the calculated field to only SUM([Div Ohead]) to ensure that the numerator of the ratio is the correct number. The resulting values should be the exact same as the numbers in the first column. Then, I would make the calculated field only SUM([YTD Fees]) to make sure the denominator matches the values in the third column.
If either of these partial calculations are returning numbers different than what you would expect to see, then that is the root of the incorrect ratio. If, somehow, both of these numbers are correct, then that means the action of dividing the numerator by the denominator is somehow causing the problem. In that case, I might try one of these options to see if this fixes things.
1. {EXCLUDE : SUM([Div Ohead])}/{EXCLUDE : SUM([YTD Fees])}
2. SUM({EXCLUDE : SUM([Div Ohead])})/SUM({EXCLUDE : SUM([YTD Fees])})
3. AVG({EXCLUDE : SUM([Div Ohead])})/AVG({EXCLUDE : SUM([YTD Fees])})
I assume the data you are working with is sensitive, but if you could replicate the issue in the sample superstore dataset or if you could change the names of confidential values in your data, a packaged workbook would be very useful in helping you figure this out.

4. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 8:35 AM (in response to Benjamin Greene)Appreciate the time that you have put into this!
However, unfortunately this didn’t resolve it.
I changed the calculated field to just say Sum([Div Ohead]) and then compared this to the first column the values were exactly the same.
I then did the same with [YTD Fees] and again, the values were exactly identical.
I then proceeded to use the LOD calculations that you’ve provided, and this is what I got:
 All values were 3%
 Again, all instances returned 3%
 As above
I’m really stumped!
I think I may just have to share a packaged workbook. It’ll take me some time replicating one using sample data.
By the way, because this was a late edit on my part I'm not sure if you picked it up in my original post the measure [Div Ohead] is a calculated field in itself. The calculation is:
IIF([Function Name (group)] = "Corporate/unallocated",[z Divisional Oheads],
[YTD Fees]/ { FIXED [Period]: SUM([YTD Fees])}
*
{ FIXED [Period]: SUM([z Divisional Oheads])})
I'm almost certain that this is causing the problem in the division, but I'm not sure how or why?!

5. Re: Computing aggregate ratio
Stephen Rizzo Oct 27, 2016 11:11 AM (in response to Adil Al Raeesi)Could you show us a screenshot of the following three fields (assuming this wasn't exactly what your original screenshot showed):
SUM([Div Ohead])
SUM([YTD Fees])
SUM([Div Ohead])/SUM([YTD Fees])
I would expect the third column to be the ratio of the first two. If that is not the case, then we may need to see a sample workbook.
Also, are you doing any blending in your workbook?

6. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 11:39 AM (in response to Stephen Rizzo)Ok, here goes:
SUM([Div Ohead])  here's what I get:
SUM([YTD Fees])
SUM([Div Ohead])/SUM([YTD Fees])  I've had to change the number format to percentage:
There's no blending going on. I'm using a single data source for this. I think I'm going to have to provide a sample workbook, but I'm not sure how I can go about replicating the [Div Ohead] Calculated field in the super sales workbook, which is what I think is causing this issue.. I'll have to figure it out.

7. Re: Computing aggregate ratio
Stephen Rizzo Oct 27, 2016 11:47 AM (in response to Adil Al Raeesi)I believe that that last screenshot that you sent is showing the correct values. To limit the effect of rounding (which is why you don't get the 5% you expected) I verified 3 of the rows with larger [Div Ohead] values:
Row 3: .46 / 15.31 = 3%
Row 6: .25 / 8.32 = 3%
Row 7: .73 / 24.43 = 3%
Is this not showing what you want it to show? If so, what would you want it to show differently?

8. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 11:53 AM (in response to Stephen Rizzo) 
9. Re: Computing aggregate ratio
Stephen Rizzo Oct 27, 2016 11:57 AM (in response to Adil Al Raeesi)I think that is working as intended. If you increase the decimals of [Div Ohead] and [YTD Fee], I suspect all of the ratios will be correct.

10. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 12:01 PM (in response to Stephen Rizzo) 
11. Re: Computing aggregate ratio
Adil Al Raeesi Oct 27, 2016 12:15 PM (in response to Adil Al Raeesi)Wait a minute! I spoke too soon. This seems to have worked all along.
Ok I feel like such an idiot right about now.
This is resolved (or was never a problem to begin with DUH!!)
Thanks Stephen for bearing with me.

12. Re: Computing aggregate ratio
Stephen Rizzo Oct 27, 2016 12:18 PM (in response to Adil Al Raeesi)No, it doesn't. All I am saying is that your problem isn't the ratio. The ratio is taking the result from the left column (SUM([Div Ohead])) and dividing it by the number in the column on the right (SUM([YTD Fee])).
Do your dimensions on the Row shelf have the same number of rows per period by any chance? Because if [Period] is not in your Rows shelf and the number of rows for each period in each row category is equal, then I believe your ratio calculation simplifies to a constant.
EDIT: Glad that helped!