An alternative to Commissions module
In the first part of this two-part blog series (see How to handle commissions in NetSuite – Part 1 ), we discussed how commission schedules in NetSuite commission module could be configured in the form of custom record types. Also, we created SuiteAnalytics Datasets that we planned to connect with PowerBI by using our connector. The schedules, along with the sales data (actuals), were then transferred to a BI tool which can manage complex calculations and presentations.
You can further learn how to accomplish this by watching the video of our recent webinar that contains a demo:
Alternatively, if you do not want to watch the video, you can continue reading this article.
We will use Nick Craig, our sales manager, as an example. In Q1, Nick’s sales amounted to $353,652.93. We have four commission ranges (commission schedules) for Nick in NetSuite commission module. Based on our fictitious rules, we apply commission to the difference between the lower and the upper range on each commission schedule. Based on our rules, if Nick’s sales equal $353,652.93, the product of 163,652.93 ($353,652.93 – $190,000.00) and .065 will give us our commission amount for a given commission schedule range ($10,637.44). See the highlighted row below.
Complete Calculation in PowerBI
Through our NetSuite to PowerBI connector, we will transfer the datasets we created in our first article (see How to handle commissions in NetSuite – Part 1). Learn more about how our connector works here.
Now, using DAX we will apply our calculation logic in PowerBI platform.
When we add up the commissions for every range, the total commission amount calculated for Nick is $16,137.44, and his supervisor’s commission equals $4,533.06.
We use PowerBI to perform commission calculations and provide reporting and visualizations because we feel that it’s a robust, inexpensive, and visually appealing platform. It is attractive for companies with a large sales force, but they find onboarding NetSuite unnecessary or expensive. Remember, PowerBI license costs $10.00 which is 1/10 of the price that companies pay for NetSuite full-user license.
Our quick example is just the tip of the iceberg in relation to what is possible when you connect NetSuite to PowerBI. We have built other operations and financial reports that we would like to share with you, all you have to do is reach out, and we will be happy to share our ideas with you.