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.

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

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

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