The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:
Stretching or compressing a forecast so that the proportion of the original series will be maintained:
This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:
Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on 🙂
As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:
So for the moment I choose between the following workaround-options:
For both options the preparations are the same:
A break-even analysis tells you at which value of the parameter in question your profit-calculation will turn positive (link). Here we need to sell at least 173 at a given price of 20 before we’ve recovered all our costs:
If your variable costs are constant, you can solve it by this formula:
BreakEvenQuantity = Total Fixed Costs / (Unit Sale Price - Unit Variable Costs)
You’ll find tons of examples on how to do this in Excel like here .
But in real life, the variable costs often depend on certain quantities as you get discounts for purchasing large amounts. The following table shows a cost structure with fix costs in row 1. The 3rd column “FixOrQty” indicates if the cost item is fix or dependent on the quantity (Qty). The 2nd row contains a variable cost that is constant with 2 for all quantities. Row 3&4 show a variable cost of 8 for quantities up to 100 and if you purchase more than 100 the costs will be lowered to 2 for all additional quantities. Row 5-7 have a similar structure, but with 3 quantity ranges:
Below you’ll find a video where you can see how easy it is to import multiples files from a Dropbox folder into PowerBI or Excel at once.
There are 2 different methods to grant access to your Dropbox: Grant access to the whole Dropbox or to a (newly created ) folder only. I will present the folder-method, as granting access to your whole Dropbox is really dangerous in my eyes – unless you are prepared to share it all publicly: The token generated will allow anyone to read your data. So also all those people who you’ve sent this beautiful dashboard where you just forgot that it contained your token…
To make it super-easy for you, I’ve created a function that you can download here: fnDropboxFolder
The code for it I’ve got from this thread in the PowerBI forum, which contains some additional useful information and a link to a solution with a custom connector for PowerBI, making it easy to deploy in a corporate environment (designed by Igor Cotruta).
Just watch how it works:
This is the last part of my little series about bill-of-materials (BOM) calculations made easy with M in Power BI and Power Query: The implosion that shows where each part is used:
BOM Where used analysis
When we look closely at the table “BOMReport” from the previous post, we see that the data is already there: “PathExplode” shows that the component “HL Spindle/Axis” (1) is used in the TopParentProduct “Road 150 Red, 62” (2) via some intermediate products (3):
BOMReport holds that information already
And if we filter on the example from the pivot-table from above, we can spot that pattern as well:
All we have to do is to create one row for each “Where-used” item. But as we have columns for the compenent itself already as well as for the TopParentProduct, we actually only want to create additional rows for the intermediate products.
Therefore we could split the column “PathExplode” up into its components. But that’s not necessary because it has been generated from the lists in column “PathItem”. So we just have to eliminate the first and the last item from those lists and expand them:
M-code to extract and expand the intermediate products
M-Code as text: BOM_ImplosionCalc1.txt
Subscribers can download the Excel-file: BOM_ImplosionCalc1.xlsx
Enjoy & stay queryious 🙂
Following up on the BOM-explosion: A comment reminded me that I had missed to present the costing techniques to calculate the total costs of each (sub)-product.
What I had shown is how to “aggregate” from parent down to child-level to retrieve the total quantity of each component within a BOM (“How many of each (sub-) components do we have to order (or build) for that bike?”) (1).
Now we reverse the aggregation direction and aggregate the total (!) quantities back up to the parents (2).
And, as this doesn’t make too much sense in an economical way, the second aggregation will be their prices (3). This will give us the sum of all part-costs (“How much will the order of all the parts cost us?”). This is also very useful for planning purposes or reconciliation of prices for intermediate products with your master data.
And if your model holds sales-data as well, you can calculate the totals costs of your total sales within each period. (4)