I’ve nearly finished the ‘Financial Reporting with Power BI’ course, really good and I am going to do this for my company. It looks like most of the fact tables are summarised/grouped already e.g. expenses (I know this is easy for demo purposes). I will be connecting to our ERP and getting the transaction tables.
My question is, is it better to use ‘group by’ in power query to group the transaction tables by values by day? In doing this the end user would not be able to drill through into the detail, is that right?
I guess it depends on what the end user wants. If they are only going to look at the top level financials, is it better to summarise/group the transaction tables in power query? Is using group by in power query going to speed up the report and take up less memory to run, than having all the detail still available to drill through to?
Just trying to weigh up what would be better. What do others do? Anyone use cross-report drill through to store the detail data in another report?
As per my experience in various roles. I would keep the transaction data in the query. Depending on performance. But normally I get a good performance by creating clean tables in power query. Then keep the DAX to the basics. Don’t throw everything in one report. Keep it to the required purpose.
Benefits for a financial are high time wise when the transaction information is included. Otherwise we will have to look everything up in the system.