12 Replies Latest reply on Oct 27, 2016 12:18 PM by Stephen Rizzo

    Computing aggregate ratio

    Adil Al Raeesi

      Hi everyone,


      Sorry I've had to blur out some the screenshot below, hopefully someone can still help me without this information.






      I've got three measure values.


      [Div Ohead] which is a calculated field. This field contains an if formula- as follows:


      IIF([Function Name (group)] = "Corporate/unallocated",-[z Divisional Oheads],

      [YTD Fees]/ { FIXED [Period]: SUM([YTD Fees])}


      { FIXED [Period]: SUM([z Divisional Oheads])})


      The third field [YTD Fees] is the sum of fees for a particular function (defined under Rows) within a particular year (defined as a filter).


      The Column in the middle is where I am having a problem. This column is a formula - [Div Ohead] / [YTD Fees] . In the first row, I would expect this to return 5.55%, however I'm getting this really odd value.


      I'm guessing this is to do with the way the values are being aggregated in the formula, however I can't seem to figure out exactly what the issue is. Can somebody help me please?



        • 1. Re: Computing aggregate ratio
          Benjamin Greene

          In 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.

          1 of 1 people found this helpful
          • 2. Re: Computing aggregate ratio
            Adil Al Raeesi

            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

              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

                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:


                1. All values were 3%
                2. Again, all instances returned 3%
                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

                  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

                    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

                      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

                        Hmm.. I'm not sure that's it. I've increased it to 9 decimal points and I'm still getting exactly the same figures.



                        • 9. Re: Computing aggregate ratio
                          Stephen Rizzo

                          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

                            The values weren't rounded- I just had the formatting so that they appeared as decimals. Removing the formatting doesn't change the ratio:



                            • 11. Re: Computing aggregate ratio
                              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

                                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!