1 Reply Latest reply on Oct 5, 2018 1:52 PM by Jonathan Drummey

    How to normalize ratio values for dynamic range

    Marcin Tymczak

      HI Guys,


      I've got a problem I cannot resolve, which is an important requirement in the reports I develop for my client.


      I've got a calculated measure called [Error Ratio] which is an aggregate:


      SUM([Error Count]) / SUM([Number of Records])


      This measure needs to be shown on a bubble chart as a color mark. Size of the bubble is error count. Bubbles represents various operating system versions. The dashboard itself is very interactive - multiple quick and action filters.


      The distribution of error ratio values is very different depending on the filter you pick on the dashboard, The are many situations when the bubble chart doesn't highlight os version that really needs an attention because:

      - there are some os version with extremely high error ratio but small amount of errors, which is not that important

      - if I filter the data for a certain period of time when the overall error ratio and error count was big, the bubble chart turns all red (because the values for particular os version are also high) and it;s really hard to distinguish them.


      I decided to normalize them using standard calc: (x - min(x)) / (max(x) - min(x)) where x is error ratio.

      The problem I face is that error ratio is an aggregate. Another problem is dashbaord interactivity - I cannot really use FIXED calc here. The dimension of the bubble chart stays the same (OS Version) but the data in the background is being filtered on multiple levels.


      I would post a workbook which would probably be more helpful, but I've got NDA on my back, and the reports are feeded from database server - anonymizing them is not an option.


      I would really appreciate your help!



        • 1. Re: How to normalize ratio values for dynamic range
          Jonathan Drummey

          Hi Marcin,


          Without seeing a packaged workbook we can be of limited help...this sounds like a good a good place for a table calculation (or some combination of EXCLUDE and INCLUDE Level of Detail expressions. Table calculations are computed after most all other calculations are completed so they avoid the filter challenges of FIXED LODs.


          ([Error Count] - WINDOW_MIN([Error Count])) / (WINDOW_MAX([Error Count]) - WINDOW_MIN([Error Count])) would do a linear scaling, you'd set the compute using all the dimensions in the view that you want to include as part of the scaling computation.