Advanced Analytics In LuckyTemplates: Threshold Triggers Using DAX

In this tutorial, I run through an advanced analytics technique in LuckyTemplates called Threshold Triggers. You may watch the full video of this tutorial at the bottom of this blog.

With this analytical concept in LuckyTemplates, you can set certain thresholds or certain trigger points via a supporting table in your data model.

Once you’ve added this supporting table and it’s inputs, you can then run logic through the table and over these thresholds. Then, assess whether your results are above or below any particular threshold.

For advanced analytics in LuckyTemplates, setting threshold triggers is a really powerful technique.

Table of Contents

• Threshold Triggers In LuckyTemplates
• Utilizing A Supporting Table
• Threshold Trigger Formula
• Conclusion

Threshold Triggers In LuckyTemplates

I wanted to find out if my customers reach a certain amount of sales or transactions, and if they reach a certain profit margin all together.

These insights are really unique and are quite helpful for your business decisions. You might want to reward your best customers if they have reached these thresholds, for example.

Furthermore, you want to be able to do this live on the fly, and so you can actually do that with the calculation that I’ve created.

As you see in this demonstration, I can click into any quarter,

and all of these results here, these threshold triggers are being met through the logic that I implement and I’m getting an updated list of customers.

and then we could have some sort of automation off the back end and it doesn’t some marketing to these customers or we give them reward points or something like that.

This is a pretty advanced technique and I’ll show you how I did it. Then, you could apply something similar in your own models in your own environments.

Utilizing A Supporting Table

I wanted to create this in a dynamic way and so I built a supporting table, which is not related or connected to anything here.

So I created this table with my target metrics with their corresponding thresholds that need to be met by the customers.

Now that I have this supporting table, I then needed to create a formula that would run through this table and evaluate each customer and the quarter. Then, it shall determine whether these customers reach these thresholds.

Looking at this table, we see that there are lots of customers. It’s quite difficult to just see the insights that we’re trying to get. And that’s why I came up with a formula that does it for us dynamically.

Threshold Trigger Formula

There’s a little bit to it, and so I’ll walk you through it so you’ll have a good understand on how you could apply this.

In this formula, I used these variables (VAR) for our threshold numbers. This goes in and extract that particular threshold number.

I wanted to see the Total Sales of these particular people, and so it’s the CALCULATE function doing that.

And then this logic with the FILTER function is running through all the customers, evaluating whether their total sales are greater than or equal to the sales threshold, and if their total transactions are greater than or equal to the transaction threshold, and profit margins as well.

If it passes all of those thresholds, then it evaluates to true and that customer will return Total Sales.

That’s how the logic works in there. You could just play around with it and see how you could implement this in your own environment.

Use DAX To Segment & Group Data In LuckyTemplates
LuckyTemplates Advanced Analytics: Secondary Table Logic Techniques
Ranking Insights Using TOPN In LuckyTemplates

Conclusion

In this blog post, I walked you through all the advanced techniques you need to implement in LuckyTemplates to make this work. I covered aspects of DAX formulas, the data model, and then showed you how you can visualize the results in a really effective way.

Think deeply about some of the applications for this analysis. A really great one is for insurance and banking with credit risk scenarios, or any other risk type scenarios.

There are so many applications where you may want to check off a number of different thresholds for certain variables or certain results in your data and then try to visualize the ones that meet set overall thresholds.

They might be outliers, they might be elements in your data that you really need to showcase or you need to focus on.

All the best.

***** Learning LuckyTemplates? *****