Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX

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:

Image1

The trick

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).

How to

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:

Image4

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:

Image5

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.
  • Image2
  • Level: The level in the hierarchy

Conditional Formatting

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):

Image6

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.

Image3

Watch out:

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”:

Summary

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.

Subscribers  (who are logged in) can download the files here:

Excel: ExcelAccountSchedModelV4Upload.xlsx

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 🙂

Comments (42) Write a comment

  1. Hi Imke,

    Thank you for this post – it is a good inspiration for the report I want to create at my company. However before I start with this, can you please advise me on the following issue:

    I have already created a multiple level hierarchy P&L (5 levels) in PowerPivot (as per Marco Russo chapter in his book) but unfortuantely it does not look as good in Power BI Desktop as in Excel.

    This my scenario in short:
    – I have chart of account with approx 300 accounts and I would like to create a short P&L with main categories like in your P&L shown in the post
    – My Fact table (GL Entry) has obviously a list of all transaction with Accounts and Amounts
    – For each P&L category multiple accounts have to be summed up (for example: Administration Costs consists of 10 accounts, IT Costs have 8 accounts and so on)

    In your FactActuals table I noticed an AccountKey is linked to every StrategyPlanKey (I guess this is like a Transaction ID). Please correct me if I am wrong but what I need to do in my scenario is:
    – clean and set up Chart Of Account with all necessary columns (Account Key, Index, Description and so on)
    – in Fact table (GL Entry) create a calculated column with Account Key where Account Number will lookup for same Account in Chart Of Account and return the Account Key
    – then play with relationships and finally create a report
    – ….

    Please let me know if my plan sounds good or if I misseed anything.

    Thank you
    Sylwester

    Reply

    • Hi Sylwester,
      things looking good in general, just some remarks:
      – you can ignore the StrategyPlanKey, it is not used anywhere in the model (I was too lazy to clean up unused columns but might do that…)
      – I wouldn’t expect that your current chart of account needs any additional work (especially if you managed the other model already). It corresponds to my DimAccount and all needed there is the AccountKey – which corresponds to your Account Number
      – so no need to create an additional column in your fact table (GL Entry): The relationship to your chart of account will probably be there already (via Account Number)
      – if you have your report table already (my “ReportsAccountsLayout”), all you need is the additional bridge table “AccountsAllocation”. I’d suggest that you build a mini-report with a handful accounts and one or two aggregations by hand to understand the mechanism and check if your model works in general as a first step.
      You’re welcome.

      Reply

      • Hi Imke,

        I managed to get it done (all tables, measures, relationships are created and work as they should) and I really like the result. At the end I had litte problems with showing empty rows or rows with dashes but then I realized there is a function “Show items with no data” 🙂

        PowerBi is a great tool isn’t it? I look forward to the time when Pivot Tables will be there as well.

        Thank you very much for this article.

        Kind regards,
        Sylwester

        Reply

        • Hi Sylwester,
          congrats – very well done!
          Thanks for letting me know and yes, PowerBI is totally awesome.

          Reply

  2. Many thanks for the example and insights! Also, thanks for bringing “Table conditional formatting improvements” to my attention; have added my votes this morning.

    Reply

  3. Perhaps, finally, David Churchwards legendary “Cascading Subtotals”-Concept has a promising successor. I´ll give it a try. Thanks!

    Reply

  4. This simply doesn’t work, Is this for some mom and Pop spreadsheet with Like 5 entries?

    Reply

    • Hi Larry,
      are you experiencing performance problems here? Where do they occur? During load or when interacting with the report?

      Reply

  5. Hello, I am struggling extremely hard to understand what in the world you are doing here. I am Creating a Report and am running into a formatting issue as well. The best I can describe it is look at this video:

    I would LOVE to use a simple formatting as you show, but even your workbook falls apart with any change at all.

    Reply

    • Hello Larry,
      I’m very sorry that this is causing you trouble and thank you very much for your heads up & the detailed description of your problem.
      As it turns out, I have missed to mention an important point in my post: When using the Index-field, you have to turn off aggregations (Don’t summarize). I’ve included this in my post above now.
      Pls let me know if you’re still encountering problems and I will study your model more closely.

      Reply

  6. Yes, That makes the format come back to where it was. I am still completely lost in what is being accomplished here though.

    I have a table called : 00-COA = Full Chart of accounts from our Accounting system

    I have a table called: 00-COASLicer = Categories, departments, Countries, etc…. It slices our chart of accounts to any requirement.

    I have a table called” BSA00GLSummary = This is the Actual Data containing the debit and Credit amounts

    What other table do I need? I simply don’t see what this report layout thing is doing. We have 4 thousand unique accounts in our chart of accounts. I couldn’t begin to imagine creating something like you have here as I simply don’t get it. Wouldn’t I end up with a table of millions of records?

    What would the relational model look like? I am not figuring out even looking at your workbook how to Create this Report Layout thing.

    00-COA —> 00-COASLICER —> Table???? —-> BSA00-GLSUMMARY

    Reply

    • Yes, your assumption that the “Table????” will become very long, is correct. But that’s no problem for the VertiPaq-engine in PowerBI, which loves long tables. The foremost authorities on this field are using a somehow similar technique like described here: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

      The analogy between our models would be this: 00-COA —> Table???? —-> BSA00-GLSUMMARY

      I don’t have a clear picture of your 00-COASCLICER -table right now and would need some further details in order to figure out how to incorporate this into my technique.

      Reply

  7. So I was right, This is only good for VERY small companies who have like 10 or 20 unique chart of accounts. There is no possible way you could maintain this with a chart of accounts in the hundreds or thousands.

    No matter what way I have tried, this solution simply does not work with any type of large dataset. It is impossible to maintain or even understand how to make all this work on a chart of accounts that has hundreds or thousands of accounts.

    Nice theory though, good solution for mom and pop businesses but NOT a solution for corporate reporting.

    Reply

  8. Hello ,

    Sorry for this long URL it is from my OneDrive,

    This link contains two files:

    1. COA = The Full Chart of accounts for all of our companies. The Main Index is the “Database Key” field which is unique for each account

    2. COA Slicer = This allows us to Slice all accounts according to Various needs. It was also an attempt at creating a model as you describe:

    So within the COASlicer there are only 3 fields I would like to group by and Aggregate data by:

    1. Type = The Highest Level Category for Each account Like (Asset, Liability, Equity)
    2. View 1 = Level 2 and Contains , Current Assets, Property and Equipment, Etc…
    3. View 2 = Level 3 and contains Descriptions like: Cash and Cash Equivelent, Dues receivable , etc…

    I would like a report categorized and displayed as the Image in the link called: What I m looking for.png

    Let me know your thoughts

    Reply

  9. The First Question I have is what fields do I need in the new Table for reporting layouts?

    I have the Fact Table: BSA00-GLSummary which is the primary fact table containing all the financial data I need. It has a Unique and Many DatabaseKey which is the exact unique key for each chart of accounts.

    The Second Table which I relate to your DimAccount is the 00-COA Table which is the full chart of accounts containing almost 4K records. It is the Raw accounts coming straight from the accounting system.

    The Third Table I have is the 00-COASLIcer table which I relate to your AccountsAllocation Table?? It contains my chart of accounts categorized and easily sliced based on whatever column is chosen, (For now I am only working on Balance Sheet Accounts)

    So the 1st question is What does the fourth table I need have to have in it? Am I relating on my Type, and View1 and View 2 fields?

    What would the layout look like?

    Index Description

    1 (Blank)
    2 Current Assets
    3 Cash and Cash Equivalent

    But How do I get the totals?

    Reply

    • Thank you for these details. Will need some time before coming back here.
      I’ve also deleted the links you’ve provided in order to maintain anonymity.

      Your modelling pattern is very familiar to me, as I’ve used it in my first multidimensional cube as well. It’s a very interesting variation to the pattern described in this article and I’m going to write a separate blogpost about it shortly.

      Reply

  10. Hello,

    After reviewing the files do you think it is still possible to create the model you show here? It is so Overwhelming with so many accounts.

    Reply

    • Sure. As I said I’m going to write a blogpost about it. Will use your data structure with randomized data items in it. But this will not happen before Christmas.

      Reply

      • That is good news indeed. I look forward to your post…:) At first I couldn’t believe it would work, but after more testing I am thinking I may be able to make this manageable, Thanks!

        Reply

  11. Regarding Excel this is a brilliant approach. I’m quite impressed and a bit stressed!

    Regarding PBI it can be simplified a lot using bidirectional filtering – neither ConsKey nor Magic Measures are necessary.

    BTW, in my understanding fnRecColumnAsList equals Table.Column

    Reply

    • Thank you, that sounds interesting!
      Have made a wide circle around bidirectional filtering so far, but if it’s useful here, I’ll give it a try. Currently working on a simplified solution of this concept without Parent-Child-Hierarchy, so might include it there 🙂

      Reply

  12. Any update to this post? I still cant get it to work with our data. Are people using real small datasets here? I still don’t see this working in a true enterprise level financial reporting environment.

    How can this be accomplished on a chart of accounts that is over 3 thousand records ?

    Reply

  13. Hi I just subscribed and downloaded the files. However when i try to go into the Power BI tables or queries, it asks for my credentials and i am unable to see how the structure for the report is set up. can you help?

    Reply

    • Hi there,
      this sample file is designed to connect to a SQL-server with the Contoso-Database I’ve linked above. You need to adjust the name of your server in the first query “MyDatabase”.
      If you want to connect to flat files instead, I’ve enclosed a zipped Excel-file with the tables that are listed under “ImportFromDB”: Just replace the code there with a reference to these tables instead.

      Reply

  14. I am trying to create a financial dashboard for a client in Power BI and struggling with how to set up the data. Would love to see the way the tables are set up and the queries that are used in this example file i downloaded from the link above so i can follow the logic through. thanks again!!

    Reply

    • Hi nk,
      I’ve now also included a zipped PBIX that already includes the sample data, so no need to connect to anything 🙂

      Reply

    • Hi navi,
      currently I cannot see how the new matrix will help here, just like a standard-pivot isn’t the perfect solution:
      1) I cannot handle asymmetric subtotals (where within the same hierarchy-level, sometimes a subtotal shall be shown and sometimes not)
      2) I produced empty/meaningless subtotals for non-balanced or ragged hierarchies.
      3) Asymetric columns (like Actuals from last 3 years, but budget figures only from current year)

      So if you don’t have any of these very specific requirements, you can of course produce nice reports using the matrix visualisation in PBI.

      Reply

  15. Hi!

    I am trying out the PowerBI solution with links to ContosoDB AccountScheduleReportsUpload.zip, which I first had to correct to the database (warehouse) ContosoRetailDW when unzipped and restored the database. I also had to change the database settings Power BI desktop. Then I tried to refresh the data, the I discovered that both FactActuals and FactPlans failed during the step FilterEntity with following error message: Expression.

    Error: We cannot convert the value “DimEntity” to type Number.
    Details:
    Value=DimEntity
    Type=Type

    The M statement it is trying to run is:

    = Table.SelectRows(UnfilteredResults, each (List.Contains(Filter[DimEntity], [EntityKey])))

    Can you help to solve this to make this run?

    Reply

    • Hi Geir,
      that’s really strange. Please check the query DimEntity like this: Check Format of EntityKey
      In my DB (Contoso German) this field is defined as an INT and comes to PBI as a Whole Number even without applying a dedicated formatting step to it. If for some reason that’s not the case with your DB, please add a format conversion step in the query DimEntity.

      Reply

  16. Thats a cool trick. It took me quite a while to understand it. Bit if I am correct
    your MyMagicMeasure is one sumif function.

    Reply

    • Thanks Robert,
      In my eyes, the whole DAX is a “Demanding Aggregate X” -concept 😉

      In this case, we can actually skip the “magic measure” and continue to work with the “original” measures who work on the Fact-table, if we use bi-directional filter in PowerBI.
      So the “magic” aspect of that measure is “just” that it allows to cross the m:n-connection between the tables that we cannot set in the model.

      Reply

      • So setting bi directional and not using the Accountsallocation (bridge) table will have the same effect? I wonder because the conskey is essential for the reporting part. As there is no conskey – acc key relation in the dim account table

        Reply

        • No, that’s not what I meant.
          What we can skip with bi-directional filter is to wrap every existing measure into the CALCULATE(..MyMeasure.., AccountsAllocation)-command.
          But we need all tables and keys for the correct “translation” for the aggregation, if we want to use a field from the “ReportsAccountLayout”-table in a report sum up all accounts correctly.

          Reply

  17. Please i need your help,

    how to divide or multiplicate KPIs using PL states (Example:KPI1=Total marketing cost/Total Sales) with using your magic allocations

    Reply

Leave a Reply