As part of my hidden gems series I want to share with you the capability of having an average function plotted inside my visuals.

Hold on (with gasps of shock) – isn’t this minor feature already inside the product?

Well yes and no.

Lumira has a few average options (no , I don’t mean that to be a slur on Lumira) however Lumira doesn’t have the one that I want to use.

Let me explain using an example;

The screenshot above shows sales plotted across days. What I want is another line (a straight line) that shows the average for all of the data plotted. Using my data I calculate that the average is 36.6 or 37 rounded up – the sum of the sales (696) divided by the total number of days (19).

Like I mentioned earlier there are some average options available to me however neither giving me the answer that I want. Let me run through these options below.

1. I can create another measure based upon sales and change the aggregation option to Average (see screenshot below).

However when I use this, the line plotted is the same as my original sales line (see screenshot below).

2. My next option involved using one of the average options available when using the Cog icon on the Y Axis for my measure Sales (see screenshot below).

Here I have Running Average and Moving Average options available to me. If I use the Running Average option, again I don’t get the desired result (see screenshot below) and the moving average option will just smooth my data (this one though is more interesting – we will come back to this later on in the blog).

3. Without going through the effort of trying the variety of average functions available surely I could have simply created a calculation:

Avg = Sales (with a sum aggregation) divided by a Sales (create a new measure but change the aggregation to Count All)

Unfortunately not. This just plots a line over the top of my original line – the same result as option 1.

**So after all of my efforts and writing what is the answer?**

Well, I hinted about the answer in option 2 – moving average. A moving average function is used to smooth the data. Used on its own I won’t get my answer, however Lumira has an extra option called “Include Self” which will help me with my result.

As long as I check this box I eventually get to the answer that I want to see (see screenshot below):

*(With my average being rounded up to 37).*

The “Include Self” option is checked if you want the first month to be included in the calculation.

At last I can see my sales with a straight line average plotted across.

I would love to have come up with this answer, however my thanks goes out to another SAP colleague.

Finally, after writing this blog, I cannot believe that I have spent so much effort writing about an average calculation.

Hope that this helps.

Dean

Hi,

Thanks for this simple yet complicated function usage with great screen shots and examples. I had a similar problem in the past and didnt check the 'include self'. This solves my long pending average calcuation ðŸ™‚

Bala.

This is wonderful and definitely solved the first step of my problem. Thank you!

I guess what I'm wondering is this, though: if this is used in a report that I'm generating every week, each week adding another line of data, am I going to have to edit the calculation every week to keep it a straight line? Right now, I've just put the largest number the field will hold in hopes that that is a sustainable solution...

Second question: this is only step one of the solution to my problem, as my real goal is to build a control chart, which means I need not just this moving average line but also +/- control lines (based on one standard deviation).

Any thoughts? Am I just wrong to hope that Lumira can be the tool that I use for this?

Hi Andrew,

Did you find out if Lumira is the correct solution for your use case? If so, could you point me in the right direction?

Thanks,

Matt