I blogged about how to Build a P&L With Power BI back in April 2020, and the response from my readers was great. Several people asked how to add percentages such as % of net sales revenue, % gross profit, etc. into the P&L. I decided therefore to do a follow up article to show how you could add percentages to the P&L and also to further demonstrate how I go about solving such problems. Keep in mind, there is rarely one way to solve a problem in DAX; I’m simply sharing the way I solved this problem.
One Solution to Add a Percentage to a P&L
I have recorded a video that steps through the process. The link to the video is at the bottom of the page. In summary, these are the steps I followed.
- I started off with the measure Total Amount as I defined in my previous article.
- I adjusted my Header table from my previous article (#1 below) and added the headers for % values (#2 below). Note that I have also added some new CalcTypes (3 & 4).
Next I wrote formulas for % of Additive Total and % of Running Total as shown below.
- Finally I updated the Total Amount measure to display the % where I wanted them to appear, as shown below.
I recorded a video showing my step by step process of solving the problem.
There is now a third post in this series that you can read here https://exceleratorbi.com.au/build-a-pl-in-power-bi-without-any-dax/