Handling multilevel bill of materials (BOM) without VBA in Excel and PowerBI is now a piece of cake: Just pass 4 parameters into the M-function below and it will return a table that holds everything you need for:
Order list (“total quantities”)
3. Implosion (“where used”): Will be covered in next blogpost
The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank:
BOM-code beautified with Lars Schreiber’s M-editor: goo.gl/KW4p8Q
txt-file for download:
The query “Invoked Function” invokes the function and needs the following parameters adjusted to your BOM-table:
- “Table”: Name of the query that holds your BOM-table
- “Parent”: Name of the column that holds the (parent) product ID or name
- “Child”: Name of the column that holds the (child) component ID or name
- “Qty”: Name of the column that holds the quantity per produced item
This query will be loaded to the datamodel and there I’ve added some DAX-PATH-columns that might come in handy for some cases.
New to M?
Watch this video where I show how to use the function code with your own data:
Details for techies and M-code-fans:
This technique is MUCH faster than the PC-solution I’ve posted here! (…just don’t ask me why & be prepared for significant performance drop offs once you try to modify anything…) It can also return the path for children with multiple parents, so an excellent workaround for this missing functionality of the DAX PATH-function (check datamodel in file). All other PATHx-functions will work, so just take the PATH from M. (Also the dynamic creating of multiple columns from the post above still works fine)
Noticed the clean code in step “AddFields”? M can look like a serious programming language once you strip off the elements that makes it a live programming language 😉
Subscribers can download the file with sample data and the pivots shown above:
Stay tuned until next week when I will post the pattern for the BOM-implosion (“where used”)
Edit 2017-May-14: Performance of this solution in Excel can decrease rapidly with larger dataset while it runs good in Power BI Desktop. Read details and workaround here.
Enjoy & stay queryious 😉
Welcome to the last part of my Easy Profit & Loss series where I will cover KPIs in rows & columns:
1) KPIs in columns
Show all your figures as a percent of turnover for example: Nice & easy: Divide current figure by the total sum of turnover:
ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report] = “Income”
We need to leave the current row context to retrieve the turnover-value in each row, therefore the ALL.
2) KPIs in rows
Reading Rob Collie’s latest cool blogpost on how to retrieve slicer selections in Power BI, I couldn’t stop thinking of how awesome it would be, if we could use this technique to pass slicer selections as query parameters to the M-queries in the query editor. Not only would we have a very convenient user interface, but – what’s actually more important at the moment – we could pass multiple values as parameters to our queries, as this is not possible at all currently:
But how to fetch them? Rob’s post simply uses cross-filtering to show the values in a separate visual. In Excel we have cubefunctions where we can pass the slicer(-selection) as a parameter. Igor Cotruta, who is describing beautiful PBI-hacks on his blog here, kindly helped me out on this: “Via DMVs. Check $system.discover_sessions for the field sessions_last_command”. This worked perfectly into the following function, in which you just have to pass the name of the measure as a parameter:
Code beautified using Lars Schreiber’s Notepad++ Script: http://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/
Make sure that you have used that measure on one of your visuals, as otherwise the function cannot harvest it. Also you have to first save the file and then push the refresh-button in order to trigger the correct refresh. The above function sort of “reads the current PBI file from outside”, so it will only see the saved version.
When you do the first refresh, a dialogue will pop up, where you just have to accept the default values like this:
The example in the file below fetches temperature data where every selected year will create a unique URL and the results of all those calls is consolidated into one table. But of course, this technique can also be used to pass multiple parameter values to SQL-commands or others.
A final note: The query to extract the slicer parameters from the DAX-statement is not particularly robust and you might have to adjust it, if your slicer-selection-strings contain special characters.
Download for logged-in subscribers:
Enjoy & stay queryious 🙂
In finance & accounting, you very rarely report the figures with the signs of their source systems, but switch (certain) signs according to different needs. Instead of using unary operators for it, I’ll present an easy and dynamic way for it in Power BI and Power Pivot using DAX. It will cover the following 3 main scenarios:
- 1_SwitchAll: All signs are switched (red)
- 2_SwitchExpLiab: Expenses and liabilities are switched back to their original values (green)
- 3_BWT_Indiv: Only the main figure for expenses (or liabilities) carries a minus, all following positions specifying the expenses are (principally) reported as positives (blue)
Switching signs in Power BI and Power Pivot without unary operators
I’m using the sample data from this article but changed the source-data to a double-bookkeeping structure. There signs are used and the transaction entries in your ledger table always add up to zero. This is a method that prevents errors when posting and can also be used to prevent errors in reporting. If you keep the signs in your reporting system, all you have to do is add up the relevant figures and the returned (absolute) figures will always be correct. If you have read my previous articles on Easy P&L, you have seen this method in action: No minus-operation there, just a simple stupid adding of all accounts who fall into several (sub-) total categories via the bridge-table.
The Account-table also contains of (sub-) totals and the column “AccountType” shows if the positions are regarded as Turnover (Revenue) or Expenses:
My values on “1_SwitchAll” corresponds to “FinalValue” in the article above. The revenues come from consultancy and coursed provided. But the revenue for courses don’t just consist of attendee rates, but the costs for catering and paid instructors shall be deducted (highlighted in yellow). So the “good” numbers that contribute to cash in your pocket shall be reported without a sign and the “bad” numbers that result in an outflow of cash shall be reported with a minus. Within the expenses category, the costs carry a minus and the travel refunds (highlighted in orange), which are cash positive, are reported as positives.
Another requirement that is often used for balance-sheet-reporting or reports that only report on cost-situations, require that the costs or liabilities are reported without signs. … Principally, because the reimbursements/cost deductions shall be reported with an opposite sign (to show the adverse effect to the cashflow). This is what “2_SwitchExpLiab” shows (not covered in the article).
Last but not least comes a typical “BossWantsThat”-requirement: Basically some strange stuff that you just have to deliver. Here the main categories “Revenues” and “Expenses” shall be shown with the signs that reflect the cash-direction, but all specifications that follow below shall be reported without signs (again: Principally, because positions with opposite cash-effects than the main category shall carry inverted signs).
Reporting techniques covered with this approach
If you use DAX to flatten Parent-Child hierarchies you will end up with a table that has a static number of columns (like described here). If you need a dynamic solution instead, which creates just as many level-columns as there are needed for the current data, you can use DAX’s helper-tool Power Query (or Get Data in Excel) or the query-editor in PowerBI, which uses the language M.
Another advantage of this solution is that you can script the table creation in one step (only flaw: You still need to manually adjust your hierarchy though): But it saves time in creating the table, especially if you have many levels.
2 simple steps
- copy the following function,
- add a new step to your current table where you call this function, filling in the following parameters:
- table name (which is the name of the previous step in your M-query)
- name of the column with the child-key
- name of the column with the parent-key
- name of the column who’s values shall be shown in the levels (can also be child-key)
And this is the code, which you can also download below:
Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂
No need to specify single accounts
So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:
- Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown
No more specification of individual accounts
How to use it:
How to use Individual Report Layout
2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)
No need to define individual group items
How to use it:
How to use Ultrashort Layout
So these 2 different layouts will both produce the same reports incl. all accounts – just like in the first example. So you can choose which layout-style suits you best – actually, you can use all 3 in parallel. You just have to make sure to grab your pivot-rows from the correct tables and in Excel to grab the matching measures, as they all have their own bridge-tables (which need to be used in the measures):
How it works
Welcome to part 2 of my series of easy Profit & Loss and other account statements in Power BI and Excel. In the first part I introduced the general principle of creating asymmetric shaped reports who use just one measure per column (you should have read this article in order to understand this post here).
How the technique works
This technique capitalises the aggregation power of the Vertipaq engine and creates a bridge-table between your DimAccount-table and the ReportsAccountsLayout-table. In there for every line of your report, all accounts that belong to the (sub-)totals are matched (“AccountsAllocation”). This table can get very long, but the engine can handle this easily:
Different use case: Account-groups-tables
In the first example we’ve worked with a chart of accounts, which had a parent-chield-hierarchy defining all the subtotals of the report. In this example we’re working with a different setup, using the good old DimAccountsGroups-table. Just one row per account and the columns are coming in pairs, containing the group-criteria and the sort-order for the report:
We also need a second table (ReportsAccountsLayout) that holds the definitions of the report-layouts like this:
While it is fairly easy to calculate the difference between 2 dates in DAX using DATEDIFF, it is a bit more demanding if you want to exclude weekends and holidays or filter the duration on certain date-intervals, so only get a part of it. Also if you want to return on date-time-level instead of only counting net-workdays.This is where this new technique for dynamic duration calculation can come in handy.
We can use the basic technique that I’ve described here and modify it by adding 2 columns to the calculated table:
- Duration per day on a Date-Time-level
- Marker-column if weekday or not (this assumes that you have a column in your date-table which indicates if the day shall be considered as weekday or not)
The duration-calculation needs to handle the cases where only parts of the day are to be counted: If the event starts and ends at the same day, the difference between those figures has to be taken. If on the other hand, the event spans multiple days, for the start-day the time until the end of the day has to be calculated while for the end-days the time from the beginning of the day is the right one. The other days count as full days with 1. Hence these 4 cases.
Let’s have a final look at our simple measures:
Alberto Ferrari has recently published a very smart concept how to analyze events with a duration in DAX, which you should read here, if you haven’t done yet. It simplifies the necessary DAX-syntax and speeds up the calculations as well. My following approach simplifies the DAX-syntax even more, but it comes with a (very tiny) premium for performance and will also increase the file size a bit. So you have the choice 🙂
I’m transforming the calculated table into a “real” fact-table which enables me to use simple 1:n-relations to the other (now) dimension-tables:
The formula starts from Alberto’s first version, but uses the Date instead of the DateKey (yellow). Then there will be some columns added which we need for following calculations (green). Then you see that the DailyProductionValue is calculated at a different place and also has a much simpler syntax. At last there are some other columns for further calculations: “Shipped” and “Ordered” will create the bridge for the “missing” connections to the date-table: