Forum Discussion

MartinTodayCake's avatar
MartinTodayCake
New Member
8 years ago

Accounting reports - adding a sub-total within a Matrix visulization

Hi

 

I am just trying to move to using Power-BI instead of Excel for some financial reports.  I am using acccounting data from an Excel PowerQuery as my source data.

 

The main fields are account code, accounting period, cost-centre analysis, and amount.

 

I am trying to create a matrix style report of the amounts, with period across the top, and account code down the side.  Cost-centre analysis is used only as a page filter.

 

So far so good, except that I now want to add sub-totals within the report to show totals for payroll costs, training costs, marketing costs etc.  If I was using Excel I would simply insert a row and sum the relevent account codes.  And to make it easier to read, I would also apply a differnt format to this sub-total row.

 

How can I do this in Power-BI please?  

 

To add a further complexity, depending on the cost-centre analysis, some account codes willl not have any amounts to report.  When I create these reports in Excel I use an indentical template for every cost-centre with a column that checks to see if there is anything to report.  If not, a filter is applied to hide the empty row.

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi MartinTodayCake,

     

    For Power BI matrix visual, you can turn on the subtotal rows under format pane.

     

    "When I create these reports in Excel I use an indentical template for every cost-centre with a column that checks to see if there is anything to report.  If not, a filter is applied to hide the empty row."

     

    Dynamically hiding record row is not supported in Power BI right now.

     

    For more advice, please share some sample data to help us understand the table structure. Also, please post the screenshot of the result which you have got in Excel to illustrate your requirement more clearly. By the way, do mask senssitive data before uploading sample data.

     

    Regards,

    Yuliana Gu

    • MartinTodayCake's avatar
      MartinTodayCake
      New Member
      8 years ago

      Thanks for the reply.

       

      I had tried 'subtotal rows' but it didn't appear to make any difference.  I also though I had solved it by using 'groups', but again once I had defined some groups, I still couldn't get them to appear in the report.

       

      The Excel version looks like this:

       

       


       

      The data is structured like this (note, the only reason for having 3 seperate fields for G/L account is because I was experimenting to try and solve the sub-total problem - ideally I would only have the G/A Account field.)

       

       

      The aim is to enable to the end-user to drill down on the reported amounts to get down to transaction detail, and also to offer some visual KPIs rather than just numbers.  I can do this in Excel using VBA macros, but it would look slicker presented in Power BI.

       

      I could add the required sub-totals as an extra field in the data table within Excel, however I am trying to avoid adding anything that is not already within the source data.  The data is held with Microsoft Dynamics Navision; at the moment I pull the data out into Excel and than manipulate it in PowerQuery.  It is only done this way because I know how to join the various tables and achieve what I need this way, but the intention is that the required SQL queries could be written and the data pulled straight into Power BI.

       

      Although dynamically hiding a record row isn't supported in Power BI, this isn't really a problem because the record row wouldn't exist in the data if it was blank.  But my point was that in the example above, the TOTAL PAYROLL subtotal needs to always sum account codes 610100 to 610900, even though in some reports 610700 (for example) won't appear in the data.

       

      Is this possible in Power BI ?