Building a P&L in Power BI – Part 2 (Percentages) - Excelerator BI

Building a P&L in Power BI – Part 2 (Percentages)

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.

  1. I started off with the measure Total Amount as I defined in my previous article.

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

  1. Next I wrote formulas for % of Additive Total and % of Running Total as shown below.

  1. Finally I updated the Total Amount measure to display the % where I wanted them to appear, as shown below.

The Video

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/

12 thoughts on “Building a P&L in Power BI – Part 2 (Percentages)”

  1. Allie Riestenberg

    Matt, your blogs on building a P&L in Power BI are brilliant! It’s complicated DAX but you present it in such a way that it’s easy to follow and understand the logic as you build on each step. Thank you for sharing you this!
    I have a question for you on a different percentage calculation. How would you build a new column in your P&L – a “% of Total Sales”? This column would take the sum of total Revenue from the Revenue row and calculate the percentage of revenue for each row below that. This calculation would need to work on both the header level and when you drill down to the subheader levels. I haven’t gotten this to work yet. Wonder if you have some insights (or would this make for an interesting “part 4” in your series)? Thanks!

    1. I thought I had done this already, somewhere. Days blur into nights, into months and years – sometimes I forget :-). My objective is always to share the process rather than the solution. The short answer is “I don’t know the formula, but you need to follow the process”.
      1. write a WIP measure that returns the Total Sales regardless of what is in the P&L visual. If you can’t get that to work, you can’t get the % to work.
      2. divide the P&L row level by the total above.
      3. if you want to be selective when to show and hide the % result, work out a true/false switch to show or hide the result

  2. Hello good day!

    First of all, thank you for the P&L video. It is being very useful in the construction of my reports, but I have a problem when comparing the current year against the previous one.

    I’m new to Power Bi and I’m learning about DAX measurements.

    Can you help me?

    1. It’s a huge topic and beyond the scope of these blogs. I suggest you ask for assistance on one of the forums, such as community.powerbi.com

      If you are after commercial support, I you can engage me to help you by purchasing some time from my shop at http://xbi.com.au/matt It should be solvable with 1 hour max.

  3. Michael Lenenkokuai

    This is a very methodical presentation. Thanks so much, I undertake to learn PBI, looks like it does have a lot of interesting ways to solve challenges.
    Thanks again!

  4. Hi Matt,

    first, thanks for the P&L video last year. This has helped me a lot. The problem described here I have also solved in almost the same way. Unfortunately, this brings up a new problem. Since the FORMAT function returns a string, difference measures (for example Total Amount A ./. Total Amount B) no longer work. If you do the subtraction before the final measure (the one with the CalcTypes) you get wrong percentages (because they now only refer to the already subtracted amounts). Another approach was a difference measure with the SUBSTITUTE function, where the % sign is removed and thus a number is returned again instead of a string. This works, but the final measure can no longer be filtered. Do you have any other ideas? I’ve been racking my brains over this for months. In any case, thanks again for the great tutorials!

    Thanks

      1. Since the created measure contains both strings and numbers, the data type variant is returned. As far as I know, this cannot be overridden.

    1. This measure is for display purposes. If it causes downstream issues for other calculations, simply create a duplicate measure and remove any formatting, then use that inside your downstream measures

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top