Lumira Hidden Gems: Average is not so Average.

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