Subscribe to receive blog updates

Search form



Dec 30, 2015

3 Basic MDX Functions Explained for Your Calculated Metrics

We go over the most basic MDX functions you might need when making calculated metrics in Slemma.

Slemma has the ability to create calculated metrics right inside the tool. This gives you the freedom to create the measures you need right when you need them instead of doing them inside your query or going back into your database.
To make a calculation in Slemma there are two ways:

  1. From the Dataset Editor (which you use when creating a new dataset or by right clicking >> Edit on existing datasets).
  2. Or right from within the dashboard editor. All you have to do is click Edit Chart Type and Data when you have a chart selected and you’ll be good to go.

Once you are in the Calculation dialogue you will see all of your metrics and dimensions on the left. This will allow you to insert each element’s unique name into your calculation. This makes creating calculations much easier.

Sum Function

The Sum function allows you to create a sum… Now, if you just want to add two measures together like [New Users] and [Retained Users], you can just use a plus sign.
The Sum function, however, comes in handy when you want to create a new metric for a subsection of a measure. The form looks like this:

Sum( Set Expression, Numeric Expression )

So, let’s say you wanted the revenue that only comes from a certain plan you offer. It would look something like this. Ignore my random spaces they are just there to make it read easier.

Sum( [Plan.Hierarchy].[Plan].[1] , [Revenue] )

Pretty easy right? The Sum function just has two parts. The first is the member that you want it to use and the second is the measure you want it to use.
If you want to have several plans, this is when you would use a Set in place of where we have [Plan.Hierarchy].[Plan].[1]. A set is a list of members all from one hierarchy. A set uses curly brackets { } and each member is separated by a comma.

Sum( { [Plan.Hierarchy].[Plan].[1] , [Plan.Hierarchy].[Plan].[2] , [Plan.Hierarchy].[Plan].[3] } , [Revenue] )

Now because the first part of the Sum function just needs a set, you can put a whole bunch of other functions in there including the Filter and Except functions. As long as the function returns a set, you can put these functions inside the first part of the sum function.

Filter Function

The filter function is super useful and like we just said, can be used in conjunction with the Sum function. This is because the Filter function returns a set of members that all that pass some logical test. If you check out our post on Calculating Churn with our Stripe Connector, you will see that the filter function is used to see if a user stayed around.
The filter function will look like this:

Filter( Set Expression, Logical Test)

So let’s break this down a bit.
What you want to do is place some hierarchy in the first part and put .Members afterwards. This just says, “Hey, I want you to look at all of the members in this hierarchy and see if it passes my test.” Then you put in a test. The following example returns a set of customers that all made a payment the previous member amounting to more than 0.

Filter( [Customer.Hierarchy].Members, aggregate( [Date.Hierarchy].[Month].Currentmember.Prevmember , [Revenue]) > 0 )

In a later post, we will go over the aggregate function but suffice it to say it checks the amount paid by each customer against 0 in this case.
The purposes of the filter function are many many many. But for starters, using it in the Sum function can produce a lot of useful metrics for you! For instance, you can use it to calculate Churned Revenue for your MRR Churn Calculation. Read more about important SaaS metrics here.
Once again, this works because the filter function returns a set.

Count (Set) Function

Now the Count function is actually really easy! All you have to do is place a set in there and it will count the number of members in the set you are giving it. It will look something like this:

Count( Set Expression )

A great application of this is calculating Average Revenue per Paying Customer.
Because we already made a handy-dandy filter function above, we can modify it so that it sees if the customer made a payment in the Currentmember simply by removing the .Prevmember.
Combine the count function with our formula above and you will get the number of customers that made a payment in the Currentmember.

Count( Filter( [Customer.Hierarchy].Members, aggregate( [Date.Hierarchy].[Month].Currentmember , [Revenue]) > 0 )

Once you have this, all you have to do is divide your total Revenue measure by this function. This will return your ARPPU.

[Monthly Recurring Revenue]
Count( Filter( [Customer.Hierarchy].Members, aggregate( [Date.Hierarchy].[Month].Currentmember , [Revenue]) > 0 )

For more on MDX, you can visit the official pages.
We hope you have found this post useful! If you need more help, just send us a message in-app or to on what you want to calculate and we will do if for you! We are always around to help :)

No comments :

Post a Comment

Share a post