This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:
The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:
- pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
- cubefunctions: who are not available in PowerBI so far
So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).
The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:
MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation)
So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:
This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:
Our bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:
The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.
Report Definition Table
At the end of your filter-cascade stands the table that holds our account-schedule-definitions “ReportsAccountsLayout”. It needs the following columns at least:
- ScheduleName: As there will be all Schedules in one table (“AccountsSchedule”)
- A row identifier: That is the key for the Account Schedules (“AccountLabel”)
- List of accounts to be aggregated: Containing a list of all account numbers that need to be aggregated in that row (“ChildrenJustLevel0”)
- Description: To be shown in the report
- Index: To provide the sort-order or the report
Of course, all this is done using Power Query and can be fully automated. The ListOfAccounts might be the most scary column here, but don’t worry: No need to type in all the account numbers into the column manually. As long as your account schema definitions contain a pattern, Power Query / M will be able to break that down and create those lists for you. In this example I’ve taken the Contoso-database (LinkToDownload) which contains a parent-child-like definition. I will explain the details on how this is done in a later post (but you find the code in the enclosed files).
And this table also contains a couple of other columns as well that help formatting:
- Indent: where the number of blanks are calculated who go in front of the description
- Account/Total: Shows whether the row is an account or a (sub)total. This is used for conditional formatting in Excel and for drilling in- & out.
- Level: The level in the hierarchy
The conditional formatting in Power BI is just very basic at the moment and I didn’t find it suitable for these kind of reports, but in Excel it works OK (this is a pivot-table with just our 4 measures):
In Excel as well in PowerBI you need to enable that rows/fields without values shall be shown. Otherwise the blank rows that structure our reports would be deleted. You will also notice that the Index-column (providing the sort-order) as well as the Level-column (providing the key for the conditional formatting) are turned to white fonds to make them invisible.
Of course: The price for this simplicity is that the signs will need to be shown.
I would suggest that you download one of the files and play around with it a bit to better understand what’s going on here. If there are particular areas of interest where you want me to provide further explanations, just drop me a note please.
When creating your report, the field “Index” mustn’t be aggregated. You either format is as a text field in your table or specifically set the summarize-option to: “Don’t summarize”:
We can use this metadata-based approach to create reasonably well formatted reports in Power BI and Excel who are fully interactive, work on the (currently limited) common denominator between these 2 programs and don’t require advanced DAX-knowledge. Consequent conditional formatting that could easily be triggered by the metadata (from the datamodel in the background) would further enhance comfort. Please vote for this feature here.
In this follow-up-post you can see how this technique can be applied to models where there are no parent-child-hierarchies but “good-old” accounts-groups-tables used instead.
New: PowerBI with data included: AccountScheduleReportsUpload_DataIncluded.zip
PowerBI with links to ContosoDB: AccountScheduleReportsUpload.zip
Sample data taken from here: https://www.microsoft.com/de-de/download/details.aspx?id=18279
Cleaned sample data in xls-format: SampleData.zip
Enjoy & stay queryious 🙂