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

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:

  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 =
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] = 1, BLANK (), [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_ =
    IF (
[Percentage] = 1,
VAR Length =
    LEN ( KPIText )
VAR MaxLength =
    MAXX (
        ALL ( IndividualAccountsLayout ),
        LEN (
            IF (
[Percentage] = 1,
& 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 (14) Write a comment

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz