Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports

As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:

  1. Right aligning text (please vote for it here: Right align text in Power BI – edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
  2. Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)

So for the moment I choose between the following workaround-options:

  1. Display %-values in a separate column
  2. Format numbers as text and fill up with spaces so that all end up right aligned
  3. See the suggestion from Matt Allington in the comments below (very nice)

Right aligning text or percentage figure in new column

For both options the preparations are the same:

Add a “PercentageFormat”-column to your “IndividualLayout”-table:

Type in “1” for every line whose results shall be presented as %.

Edit query “IndividualAccountsLayout”

In step “ExpandScheduleData” you have to click the new field “PercentageFormat” to be expanded as well.

Create a new measure

Percentage =
IF ( VALUES ( IndividualAccountsLayout[PercentageFormat] ) = “1”10 )
DAX Formatter by SQLBI

This makes subsequent measures easier to write.

1) Display %-values in a separate column

We create a new measure that just shows %-values:

Plan_% =
IF ( [Percentage] = 1, [ActKPI], BLANK () )
DAX Formatter by SQLBI

Format this as percentage:

And another measure that’s not showing % any more:
Plan_A =
IF ( [Percentage] = 1BLANK (), [PlanKPI]

DAX Formatter by SQLBI

That’s easy DAXing, but not superchic.

2) Format numbers as text and fill up with spaces so that all end up right aligned

The following measure dynamically evaluates the maximum length of the measures text-representation and fills up with spaces depending on the individual text-length. Unfortunately the spaces don’t require as much space as ordinary numbers, so we have to take 2 spaces for one number. As you will see, the alignment is not really exact here. If you know another (unicode) sign that is more precise, please shout.

ActKPI_ =
VAR KPIText =
    IF (
[Percentage] = 1,
        FORMAT ( [ActKPI], “Percent” ),
        FORMAT ( [ActKPI], “Standard” )
    )
VAR Length =
    LEN ( KPIText )
VAR MaxLength =
    MAXX (
        ALL ( IndividualAccountsLayout ),
        LEN (
            IF (
[Percentage] = 1,
                FORMAT ( [ActKPI], “Percent” ),
                FORMAT ( [ActKPI], “Standard” )
            )
        )
    )
RETURN
    REPT ( UNICHAR ( 8287 ), ( MaxLength – Length ) * 2 )
& KPIText
DAX Formatter by SQLBI

For very large models (long layout-tables) performance might be impacted. Also you will not be able to do any conditional formatting based on values, because the figures have been converted to text.

If you need help implementing this solution: I’m offering remote setup- and training services for my models. So just send me a mail: info at thebiccountant.com and we can evaluate what’s needed.

Enjoy & stay queryious 🙂

Comments (11) Write a comment

  1. I always keep a separate column with % relative to Sales (output-value).
    …to know each dimensions relative value; f.ex. ‘flow-unit’, ‘process’, ‘resources’, ‘control’, etc
    …for the B.arch. interlingua 😉

    Reply

    • Yes, it also has advantages to show them in a separate column, as you can quickly spot them.
      But often the design requirements are really narrow and people are not too keen on any changes to them.

      Reply

    • Thank you Matt, missed that idea!
      Your suggestion is great:

      Matt Percentage

      If anyone knows how to narrow the gap between the 2 columns, please let me know. Thanks.

      Reply

        • Thanks Gilbert!
          Yes, that slims the column, but doesn’t drag the % closer to the last number from the previous column.
          So there still is the wide gap between the %-sign and the corresponding numbers.
          But it’s good to know, as one doesn’t want any column headers for those %-columns here.

          Reply

  2. Been toying around with this a lot. As it’s very cumbersome modifying the layout tables once you have introduced them to the data model I’ve deployed a table config approach which can be easily maintained

    This involves having an excel file as a data source and using that to drive the creation of the tables in power BI. So your individual layout table would actually be created from the excel file

    Incorporating the suggestion above, one of the columns within it would be a display type value (% or actual) and another would be an alignment column

    Will share an example once I have it up and running

    Reply

    • Sounds good! Looking forward to it.
      I wouldn’t expect any of the tables being maintained in the pbix at all. They should all be imported, otherwise you couldn’t have more than one pbix-file for your reporting.
      Besides the layout-table, which will constantly be edited for new report layouts or changes, the account-group-table will have to be edited quite frequently: Everytime a new account is added, all the existing group-columns have to be filled. My advise is to make this part of your closing checklist before releasing any reports for the period.

      Reply

  3. I’m thinking along the lines of having an Excel source file called TableDefs. Within it would be a few tables which can be easily maintained. So by the same token you’d have a table within for the AccountsGroup & Accounts as well. I think some check columns might be handy as well to indicate everything is properly ‘glued’ together
    Based on my experience anything to help with ease of setup and ongoing maintenance would be valuable

    Reply

  4. “Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)”

    I hope that this never happens. I’ve added a comment in Matt’s request indicating why this is a bad idea. The correct place for conditional number formatting is in conditional formatting (the presentation layer, not the layer that calculates a value) – just like it is done in Excel.

    Reply

Leave a Reply