Microstrategy derived metrics

I’ve been using Microstrategy for more than a year by now, never used derived metric before until recently discovered the beauty of derived metric.

Derived metric, a Microstrategy’s term, I’d rather call it report function. Basically it’s a function that works at report level. The standard metric can be shared across various reports while derived metric is tied to a particular report. It’s dynamic in the sense it can be used together with other standard metrics, for example you can put logic in to show different values(calculated by different metrics) in a single row/column:

For example I want to have a report to show different figures for past three years: attribute Year and two metrics A and B are used. A derived metrics is created to find out the most recent year and another derived metric is created to show either A or B dynamically. The functions you’d use is max(to get most_recent_year), another max(curr_year, target at Year level) to get year of current column then case (most_recent_year=curr_year,metricA, metricB).

Whenever some calculation needs to be done on the fly, think about derived metric first!

[widgets_on_pages]