Forum Discussion
Accounting reports - adding a sub-total within a Matrix visulization
Microsoft EmployeeHi 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
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 ?