Dynamically scale your key figures for even more intuitive dash boarding

Dear all,

When it comes to displaying key figures, we usually assign scaling factors with their prospective magnitudes in mind. But what if the magnitudes scatter a lot depending on filtering or key dates? Or even more crazy – your client wants to define his own threshold when to change factors. That is when dynamic scaling comes into play.

There are two approaches that I would like to discuss today:

• Front end solution using DesignStudio scripting only
• Clever BEx Query calculations

Implementing the first option, you could keep your data sources as is and deal with the runtime data on DesignStudio scripting. You would need to code some loops to determine the maximum of your data series and use that information to scale the measures (setScalingFactor) accordingly. Implementing it like this works but adds additional load due to the looping and changing of the scale factors. In addition to that, it does not scale well because any data source change causes maintenance efforts to adapt your scripts.

The second option however does not. Obviously, it would be far nicer to solve the scaling requirement already before the data is loaded to avoid revising it. To do this in BEx, create a formula to determine the required scaling factor.

Formula A: Helper Function for scale calculation

For example:

Actual Year: 775,791,234.12

Last Year: 627,712,413.32

Threshold: 500,000 (everything bigger than half a million shall be scaled as million), defined as helper cell on BEx Query Designer.

The most inner logical expression evaluates to 775,291,234.12, which is bigger than zero and therefore will be counted as one using the last logical operation COUNT. Basically, the given function above always calculates a one or a zero. We reuse that information to determine if we want to apply a scale of 1,000 or 1,000,000. Of course, this approach could be enhanced to work with more than two states, but for most cases this is already sufficient.

Formula B: Calculate scale

1000 + ‘Formula A’ * 999000

Now you can use formula B to scale your actual key figures by 1,000 or 1,000,000. It makes sense to display the calculated scale (formula B) on the query as well to notify your users about the scale change. Otherwise, this might be misleading because the standard scaling factor is not applied.

Furthermore you can use this information to fill DesignStudio text objects describing values on a chart for example (e.g. numbers given in millions etc.).

Fig.1 Text example using scale information in footer

For table-based display, I would suggest to highlight the row with the calculated scaling factor to emphasize it.

One last comment: Keep in mind that you need to re-run the DesignStudio script that updates the footer text of the chart to reflect possible scaling changes accordingly.

That shall be it for today.

I would like to give a special thank you to Christian Stechow who pointed me into the right direction to implement this approach.

As usual, feel free to leave comments and ask lots of follow up questions.

Yours

Martin