Open Forum

Expand all | Collapse all

Power BI for Customer Margin

  • 1.  Power BI for Customer Margin

    Posted Jan 08, 2019 10:00 AM
    Our 2019 initiative is to be able to report profit margin by customer.  Once we upgrade from Excel 2007 shortly, we plan to implement Power BI.

    Looking for some direction on the best way to accomplish this type of reporting. Here are some things that make us interesting:

    We are not currently using the Sales/ A/R module. Customers are ACH'ed for services from a separate billing system at the end of each month. No inventory, no A/R.  These transactions are currently flowing through as a Bank Transaction in a lump sum.

    We are not adverse to loading customers, and uploading the invoice data into the module. However, do not want to go to the item level within GP for cost.

    Expense invoices will be entered into the GL at the customer level, description within the GL will identify customer. Will not be using sub ledgers for customers. Trying really hard not to go that route anyway.

    We were hoping to be able to pull reporting in Power BI by these descriptions from the select revenue and expense GL's.  Or a mix of revenue data from the Sales Module, against the expense GL by description.

    How crazy does this sound? Is this possible?  Open to ideas and suggestions.

    Katie Graves
    Accounting Manager
    Illinois Credit Union System
    GPUG Chicago Chapter Leader

  • 2.  RE: Power BI for Customer Margin

    Posted Jan 08, 2019 11:17 AM
    Hi @Katie Graves

    You can always upload the invoices as RM transactions and just record the "Cost" - if you know it from the other system as a lump sum.

    In the screenshot below you can see that my "Sales" or the total invoice is for $10k - which is what the customer pays me.  You can also record the cost manually - which is the COGS type distribution.

    You will need to offset the COGS with an INV (inventory) type distribution, but this could be a sort of "holding" account - you'd need to work out the accounting for that somehow - you could even increase the actual "sales", etc - to account for the income before cost factor.. second screenshot.

    Just some thoughts...

    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)


  • 3.  RE: Power BI for Customer Margin

    Posted Jan 09, 2019 11:42 AM

    Its possible based on your description of your systems.  One of the best parts about Power BI is its ability to get data from multiple data sources (GP and your billing system in this case) and create relationships between the sources so you can report on customer margin. So this is not an impossible task but will take some extra work because of your system setup.

    You said Expense Invoices will be entered into the GL at the customer level.  Are you hand entering these or integrating them with integration manger or SmartConnect?  I ask because if you could use a extra field like Originating Master ID to store the customer number when entering Expense Invoices this will make creating the source relationship easier in Power BI.  Description may not be the best field for storing the customer number because some users may enter the customer number at the beginning and other at the end, etc.

    Leslie Vail has a free GL Transaction Entry modification that would make the Originating Master ID field available if you are hand entering the expense invoices.

    Barry Crowell
    Senior Business Consultant
    KTL Solutions, Inc
    Frederick MD

  • 4.  RE: Power BI for Customer Margin

    Posted Jan 09, 2019 04:27 PM
    As Barry mentioned we use the Originating Master ID to store Customer ID's for expenses that get coded directly to the GL.  We then pull the data together with revenue from Project Accounting but same concept as AR using SQL view and display in PBI.  Regardless if you are using Description or Originating Master ID the issue is ensuring the data is accurate.  You will need to monitor in your case the Description field with a report or alert of some type to look for invalid or missing data.  If you have the ability I would look to automate the integration of the GL expense entries as Barry mentioned to ensure you are coding the account numbers correctly from the start.

    Good luck this can get a little messy but fun :-)

    Kerry Draper
    Business Analyst
    Taos Mountain, Inc.
    Boise ID

  • 5.  RE: Power BI for Customer Margin

    Posted Jan 10, 2019 09:54 AM
    Thanks for the feedback @Jo deRuiter @Barry Crowell @Kerry Draper

    We do plan to use integration manager to get all of this data in​​​​, each transaction is well over 1,000 lines when we break it down by customer.  I'm glad to know Power BI is as customizable as we hoped. We will keep moving forward, and see what happens!

    Katie Graves
    Accounting Manager
    Illinois Credit Union System
    GPUG Chicago Chapter Leader

If you've found this thread useful, dive deeper into User Group community content by role