Nigeria Modern Excel & Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Preparing Reports from Trial Balances in Power BI

  • 1.  Preparing Reports from Trial Balances in Power BI

    Posted Jan 25, 2018 04:35 AM

    I have a challenge in Power BI that I need help with.

    I am using Power Bi to prepare P&L and Balance Sheet reports from downloaded Trial Balances. Is this something you have done before? Is it possible?

    How can I go about it?



    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------


  • 2.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 25, 2018 04:59 AM
    Hi Abiola,

    It is possible to create Financials with Power BI. What are your specific challenge?

    Regards.


    Click HERE to See what we do


    Ahmed Oyelowo

    Analyst, dbrownconsulting


    w: www.dbrownconsulting.net

    Cell: +234 (0)805 801 7067


    Lagos Office 1 (Training Centre)

    70A Adeyemo Akapo Street

    Omole Phase 1, Ikeja. Lagos.

    Tel:  +234 (0)700TRAINING

    Tel:   +234 (0)700PAYROLL


    Lagos Office 2

    3rd Floor, Africa Re House,

    Plot 1679, Karimu Kotun Street,

    Victoria Island. Lagos. Nigeria.

    Tel:   +234 (0)701 375 0888


    London Office

    Landmark House

    17 Hanover Square

    Mayfair, London. UK

    W1S 1HU

    Tel:   +44 (0) 2075291496


    Accra Office

    7th Floor, GNAT Heights

    30 Independence Avenue

    Ridge, Accra, Ghana

    Tel:  +233 (0) 30 7010976






  • 3.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 25, 2018 05:13 AM
    I need to be able to calculate the Gross Profit, PBIT, PBT, etc., and present them along with Revenue, COS, etc.

    I have attached a screenshot of the issue.

    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------



  • 4.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 27, 2018 08:30 AM

    Dear Abiola,


    The way to go about this is a bit out of the box. Your report is filtering out the Gross Profit & Net Profit Lines that you want to see because the table relationships is flowing to the Fact file (GL Table) and that does not have Gross Profit, Net Profit and the likes.  Follow the guides below to resolve the issues:

     

    Tables


    You will need to have the following tables:

    1.       Fact Table: Basically your GL account. (GL_Table).

    2.       A dimension table (Accounts_Table) for all accounts: More like GL Mapping. However, you need to include lines for Gross Profit, Net Profit and other aggregation lines you want to see in your P&L.

    3.       A dimension table (PL_Lines_Table) for all lines needed in your P&L. Sorted and sequential in order of appearance on P&L 

    Columns required in the tables


    The tables listed above should contain the following fields:

    1.       GL_Table: Date; Account Name; Amounts (You should have only one values column. Use Power Query to unpivot the table. You most likely need only the closing balances so you can filter out debits and credits.

    Date

    Account

    Amount

    31/01/2013

    Depreciation

    24,071.00

    31/01/2013

    Finance charges

    52,927.00

    31/01/2013

    Store A revenue

    114,343.00

    31/01/2013

    Store A revenue

    52,669.00

    31/01/2013

    Store B revenue

    11,638.00

    31/01/2013

    Admin & General

    22,886.40

    31/01/2013

    Advert & Sales

    9,088.40

    31/01/2013

    Heat, Light & Power

    17,802.50

    31/01/2013

    Repairs & Maintenance

    12,581.20

    28/02/2013

    Depreciation

    24,071.00

    28/02/2013

    Finance charges

    52,613.00

    28/02/2013

    Store A revenue

    132,533.20

    28/02/2013

    Store A revenue

    53,654.00

    28/02/2013

    Store B revenue

    20,283.90

    28/02/2013

    Store A cost

    10,529.60

    28/02/2013

    Store B cost

    18,446.30

    28/02/2013

    Admin & General

    29,778.20

    28/02/2013

    Advert & Sales

    8,453.20

    2.       Accounts_Table: Account Name; Account Category; Operator Sign for each account (e.g, revenue is +ve, cost of sales is -ve. Use 1 to represent +ve and -1 to represent -ve).

    Account

    Account Category

    Sign

    Finance charges

    Finance charges

    -1

    Depreciation

    Depreciation

    -1

    Store A revenue

    Revenue

    1

    Store B revenue

    Revenue

    1

    Store C revenue

    Revenue

    1

    Store A rent cost

    Cost of sales

    -1

    Store B rent cost

    Cost of sales

    -1

    Admin & General

    Admin Expenses

    -1

    Advert & Sales

    S & D expense

    -1

    Heat, Light & Power

    Admin Expenses

    -1

    Repairs & Maintenance

    Admin Expenses

    -1

    Other Income

    Other Income

    1

    Gross margin

    Gross margin

    1

    Other Indirect expenses

    Other Indirect expenses

    -1

    Profit before interest tax (PBIT)

    Profit before interest tax (PBIT)

    1

    Profit before tax (PBT)

    Profit before tax (PBT)

    1

    EBITDA

    EBITDA

    1

    Total operating expense

    Total operating expense

    1

    Net Income

    Net Income

    1

     

    3.       PL_Lines_Table: Order Number; P&L Lines; Calculation type (Use 1 for the default lines in your GL and 2 for aggregation lines you are adding in i.e Gross Profit, Net Profit etc).

    Order

    PL Lines

    CalcType

    1

    Revenue

    1

    2

    Cost of sales

    1

    3

    Gross Margin

    2

    4

    Other Income

    1

    5

    Net Income

    2

    6

    S & D expense

    1

    7

    Admin expenses

    1

    8

    Other Indirect Expenses

    1

    9

    Profit Before Interest Tax (PBIT)

    2

    10

    Finance charges

    1

    11

    Profit Before Tax (PBT)

    2

    12

    Depreciation

    1

    13

    EBITDA

    2

    14

    Total Operating Expense

    1



    Relationships

    You should create the following relationships between the three tables:

    1.       Accounts column in Account_Table and Accounts column in GL_Table

    2.       Account Category in Account_Table and Account Category in PL_Line_Table

    Measures

    You should write the following measures:

    1.       Amount Sum = SUM(GL_TABLE[Amount Column])

    2.       PL Amount = SUMX(Accounts_Table, [Amount Sum] * Accounts_Table[Sign])

    3.       Calculation Type = MAX(PL_Lines_Table[CalcType])

    4.       Running Total = IF(HASONEFILTER(PL_Lines_Table[PL Lines], CALCULATE([PL Amount], ALL(PL_Lines_Table), PL_Lines_Table[Order] < VALUES(PL_Lines_Table[Order])), BLANK())

    5.       Final Amount = SWITCH([Calculation Type], BLANK(), BLANK(), 1, [PL Amount], 2 [Running Total])

    Explaining the measures

    1.       Amount Sum: This is to sum the amounts column in the fact file

    2.       PL Amount: This is to multiply each line item by the sign and sum row by row. So if Revenue is 100 and the sign is 1 you have 100, Cost of sales is 30 and the sign is -1, you have -30. The sum of both of which is now 70

    3.       Calculation Type: Get the calculation type of each line item as defined earlier. (i.e 1 for default lines and 2 for aggregation lines). This calculation is used in the final measure.

    4.       Running Total: The initial problem is that the gross profit, net profit and the likes get filtered out after you drop your MEASURE in the report because those lines are not in your fact table. So we calculate a running total, that: If the PL_Lines_Table gets filtered (as you would expect), DAX should do a CALCULATE  of PL Amount and sum every line that is above the filtered line. Eg, if Gross profit is line 3, DAX should sum lines 1 and 2. Which is 100 and -30 to give you 70.

    5.       Final Amount: If calculation type (of the PL_Line_Table) is blank, return blank, if it is 1 (default lines), return the value for PL Amount, if it is 2, return the value for Running Total.

    If you follow this guide, your challenge should be solved.

     



    Regards.


    Click HERE to See what we do


    Ahmed Oyelowo

    Analyst, dbrownconsulting


    w: www.dbrownconsulting.net

    Cell: +234 (0)805 801 7067


    Lagos Office 1 (Training Centre)

    70A Adeyemo Akapo Street

    Omole Phase 1, Ikeja. Lagos.

    Tel:  +234 (0)700TRAINING

    Tel:   +234 (0)700PAYROLL


    Lagos Office 2

    3rd Floor, Africa Re House,

    Plot 1679, Karimu Kotun Street,

    Victoria Island. Lagos. Nigeria.

    Tel:   +234 (0)701 375 0888


    London Office

    Landmark House

    17 Hanover Square

    Mayfair, London. UK

    W1S 1HU

    Tel:   +44 (0) 2075291496


    Accra Office

    7th Floor, GNAT Heights

    30 Independence Avenue

    Ridge, Accra, Ghana

    Tel:  +233 (0) 30 7010976






  • 5.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 30, 2018 04:14 AM
    Thank you Ahmed. This was very helpful.

    I was able to see the category 2 lines in the Grand total column. This shows progress for me, and it introduces a new challenge as well.

    1. How can I make this category 2 rows show in the monthly display of the financials?
    2. How do I remove the Grand total column on a matrix table?

    Thank you very much for your support so far.

    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------



  • 6.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 30, 2018 07:19 AM
    Hi Abiola,

    For your category 2 lines to show, you need to confirm that your connections and filter directions are correct.

    To turn off Grand Totals, click on the affected report, in the Visualizations pane, click on Format, in Subtotals, change Row subtotals and Column subtotals to "off".

    Regards.


    Click HERE to See what we do


    Ahmed Oyelowo

    Analyst, dbrownconsulting


    w: www.dbrownconsulting.net

    Cell: +234 (0)805 801 7067


    Lagos Office 1 (Training Centre)

    70A Adeyemo Akapo Street

    Omole Phase 1, Ikeja. Lagos.

    Tel:  +234 (0)700TRAINING

    Tel:   +234 (0)700PAYROLL


    Lagos Office 2

    3rd Floor, Africa Re House,

    Plot 1679, Karimu Kotun Street,

    Victoria Island. Lagos. Nigeria.

    Tel:   +234 (0)701 375 0888


    London Office

    Landmark House

    17 Hanover Square

    Mayfair, London. UK

    W1S 1HU

    Tel:   +44 (0) 2075291496


    Accra Office

    7th Floor, GNAT Heights

    30 Independence Avenue

    Ridge, Accra, Ghana

    Tel:  +233 (0) 30 7010976






  • 7.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Jan 30, 2018 12:56 PM
    Awesome.
    This did the magic.

    Thank you very much. I am grateful for your assistance.

    I will surely reach out if I have any challenge in future.

    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------



  • 8.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Feb 02, 2018 03:13 AM

    Good Morning Ahmed,

    Thank you for helping resolve the challenge I had with preparing P&L report from the Trial Balance.

    Now I have a new challenge with preparing the Balance sheet. Using the SUM function aggregates the balances. I am of the opinion that a filter should be used.
    Kindly assist with the DAX expression to be used.



    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------



  • 9.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Feb 02, 2018 11:44 AM
    Hi Abiola,

    If you already have a formula that sums the balance sheet amounts from the GL Table, write another measure that gives you the ending balances only.
    Ending_Balance = (CALCULATE([Amount_Balance], FILTER(GL_Table,GL_Table[Date] = MAX(GL_Table[Date]))).

    You should then use the above formula in your Balance Sheet Running Total Formula.
    BS_Running_Total = IF(HASONEFILTER(BalanceSheet_Lines_Table[Balance sheet lines]),CALCULATE([Ending_Balance], ALL(BalanceSheet_Lines_Table), 'BalanceSheet_Lines_Table'[Order] < VALUES(BalanceSheet_Lines_Table[Order])), BLANK())

    Your final formula to be dropped in your report table will be
    BS Final Amount = SWITCH([BS Calculation Type], BLANK(),BLANK(),1,[Ending_Balance],2,[BS_Running_Total])

    Regards.


    Click HERE to See what we do


    Ahmed Oyelowo

    Analyst, dbrownconsulting


    w: www.dbrownconsulting.net

    Cell: +234 (0)805 801 7067


    Lagos Office 1 (Training Centre)

    70A Adeyemo Akapo Street

    Omole Phase 1, Ikeja. Lagos.

    Tel:  +234 (0)700TRAINING

    Tel:   +234 (0)700PAYROLL


    Lagos Office 2

    3rd Floor, Africa Re House,

    Plot 1679, Karimu Kotun Street,

    Victoria Island. Lagos. Nigeria.

    Tel:   +234 (0)701 375 0888


    London Office

    Landmark House

    17 Hanover Square

    Mayfair, London. UK

    W1S 1HU

    Tel:   +44 (0) 2075291496


    Accra Office

    7th Floor, GNAT Heights

    30 Independence Avenue

    Ridge, Accra, Ghana

    Tel:  +233 (0) 30 7010976






  • 10.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Feb 02, 2018 01:23 PM

    Awesome!

    This is just perfect. Problem solved.

    I really appreciate all your time and effort.



    ------------------------------
    Abiola Sanni
    Senior Manager
    Teleios First Resources Ltd
    Lagos
    8029291105
    ------------------------------



  • 11.  RE: Preparing Reports from Trial Balances in Power BI

    Posted Nov 05, 2019 07:51 AM
    Great!! Could you please help me out having one more row i.e., GOP%

    Revenue
    Expenses
    Gross Profit
    Gross Profit %

    Thanks a ton!!

    ------------------------------
    Suresh Babu
    Asset Management
    ------------------------------