8 Replies Latest reply on Oct 26, 2016 8:53 AM by Ben Young

    How to Normalise Data to Show Relative Measures?

    James Hillier

      I am trying to produce some line graphs but the measurements for the Y axis are in the tens of thousands.


      I would like to have the graphs show the highest intensity of one of the samples, a control, as '1' so that all other measurements can be given relative to this.


      To explain this in different words, instead of the Y axis being on a scale of 0 - 150,000, I would like it to be more like 0 - 1.5, with 1 being the height of the positive control.


      Any ideas how I could go about doing this?

        • 1. Re: How to Normalise Data to Show Relative Measures?
          Ben Young

          Hi James,


          Do you have a sample workbook you could attach? That would give us a context to try and help you out. That being said, I'll give it my best shot given your questions.


          Given your data, you could pass through a straight calculated field that takes you measure and divides it by 100,000. This will scale it back to your 0-1.5 scale. From there, you could use a parameter or a constant line to draw a line at 1. This will persist, regardless of how your data moves.


          Looking at your question though, it looks like the "1" you'd like to normalize to isn't the actual value of 1.0, but is some number derived from your data. If that's the case, would you please explain in a bit more detail what you would like your control to be?





          • 2. Re: How to Normalise Data to Show Relative Measures?
            James Hillier

            Hi Ben,


            Thank you for your response.


            I am working with biochemical data. The X axis is time and the Y axis shows fluorescence. Basically higher fluorescence = more protein. This is a result of a chromatography process to purify proteins, hence the time dimension. Now, the fluorescence is currently in arbitrary units. However, I would like to change this so that it is actually the relative fluorescence compared to a positive control sample, the positive control protein that I know what to expect for. The maximum height of the peak for this protein would be 1. I hope this makes sense.


            I have attached the workbook. If you go to sheet 1 or dashboard 1, you will see the type of graph I am trying to produce. As you will see, the X axis shows large numbers but I would rather it was simply the relative fluorescence from the sample (F1B1) to the control (Smo).


            Thanks again



            • 3. Re: How to Normalise Data to Show Relative Measures?
              Ben Young

              Happy to try and help. I'm glad there are smart people out there like you playing with awesome things like chromatography and protein fluorescence


              Attached is my first attempt. I do not think it is what you're going for, but I wanted to make sure I was heading the right direction first. What I did is provided a graph that shows the difference from the maximum of your control (roughly, max(control) - sum(F1B1); there's a bit more nuance you can look at in the attached file). This may be what you're looking for.


              However, my guess is you're actually looking for the difference between your control and your sample at every point along the axis, not just a difference from the max. But, you wanted to correct the maximum to only go to 1 since the units are arbitrary. Is that correct?



              • 4. Re: How to Normalise Data to Show Relative Measures?
                Ben Young

                And here's what I think you were going for. I made a calculation that normalizes your Smo data based off of it's maximum value. This basically removes the units and puts the entire graph on a scale of 0-1, with 1 being the maximum of your control group.


                Here's the calculation: sum(Smo)  / window_max(sum(Smo)) - sum(N1B1) / window_max(sum(Smo)). The window_max(sum(Smo)) finds the maximum value for your control on your view. Dividing by it puts everything on the same scale. The visualization using it is on the BY - Attempt 2 tab.


                Hope this helps!



                • 5. Re: How to Normalise Data to Show Relative Measures?
                  James Hillier

                  Thank you for this Ben.


                  This isn't exactly what I'm looking for but it is close. Essentially the lines should be the same shape as they are in Dashboard 1. I literally just want the X axis changed so that the highest point on the line for Smo is equal to 1. So in terms of the units in this example, 1 would be equal to ~174,000. I just want to be able to directly compare the height of the peak for the sample to the height of the peak for the control, 1.


                  Is this possible?


                  I really appreciate your help!

                  • 6. Re: How to Normalise Data to Show Relative Measures?
                    Ben Young

                    OK, here's another couple of my attempts (on tabs BY - Attempt 3 and BY - Attempt 3.5). I did my best to match what you showed in Dashboard 1. In attempt 3 I did it only with F1B1. In Attempt 3.5, I made the guess that this is what you wanted, so I built in a bit of flexibility for you so you could actually scroll through each measure, based off of a parameter choice. Feel free to ignore if you don't need it.


                    Hope this helps! If not, I'll work on a better answer for you

                    • 7. Re: How to Normalise Data to Show Relative Measures?
                      James Hillier

                      Thank you! Attempt 3 is exactly what I was hoping for.


                      Please could you tell me how I would go about applying this to other samples?


                      Thank you very much for your hard efforts.

                      • 8. Re: How to Normalise Data to Show Relative Measures?
                        Ben Young

                        Excellent! Glad we finally got there in the end As far as applying it to other samples goes, you have a few options:

                        1. The most manual way to go about it would be to create a calculated field for every single sample and have different views for all of them. This likely would create way too many individual sheets to be very practical.

                        2. You could use the Measure Values field and show all the calculated fields (one for each sample) on the same view, although this would still require all the calculated fields and would be a bit tough to read. It could be very useful though if you combine it with a filter using the Measure Names pill to allow the user to explore and compare multiple samples on the same view. If you need to compare multiple samples at the same time, I would probably use this approach.

                        3. On the BY - Attempt 3.5 tab, I modeled a third approach where you use a parameter to change the sample you pass through to the calculated field. It lets you choose samples (one at a time) to compare to your control group.


                        There is a fourth option that combines 2 and 3, but it would take a bit of work to build and explain, so I didn't list it for now. Happy to include it if interested.


                        Do any of these options work for you?