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:
- 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/)
- 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:
- Display %-values in a separate column
- Format numbers as text and fill up with spaces so that all end up right aligned
- 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”, 1, 0 )
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 () )
Format this as percentage:
And another measure that’s not showing % any more:
Plan_A =
IF ( [Percentage] = 1, BLANK (), [PlanKPI]
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
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 🙂
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 😉
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.
Nice tips. I wonder how hard it would be to have a second column that just contains a % symbol in its own column. Also people can vote here for conditional formatting of switch measures https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-measures-using-switch
Thank you Matt, missed that idea!
Your suggestion is great:
If anyone knows how to narrow the gap between the 2 columns, please let me know. Thanks.
What I have done in the past is to use the new Rename on the column name and rename it to “” (blank) this then allows you to make it as small as possible.
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.
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
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.
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
Definitely 🙂
“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.
Hi Imke
Commented a while ago on how awesome you Easy PL is and am still working through the solution for the business I work in. I wonder if you could help with a tricky one however.
My balance sheet is in the fact table as BF + Mvmnt in January, then mvmnt for all other months. So balance sheet balances are easy, TOTALYTD or Running totals.
But I need to calculate Average balance sheet values over 12m, 6m, and 3m. The forum sites offer this kind of pattern:
Test12m = CALCULATE([BS Running Total],
FILTER(ALL(‘Calendar’),
AND(‘Calendar'[Date] <= max (‘Calendar'[Date]),
DATEADD(‘Calendar'[Date],1,YEAR) > max(‘Calendar'[Date])
))
)
But in my data this will never work because the data is not there for the BF of the furthest month back in time unless this is Jan.
So the question is what is the DAX to sum YTD amounts for the last 12m? ie Jan YTD + Feb YTD + Mar YTD ……+ Dec YTD. Once I have this I can div by 12 and I’m all sorted!
Any help greatly appreciated.
Pete Smith
Hi Pete,
I think I’m missing some bits from your scenario: How exactly do you calculate your BS balances?: BF just for the first year or do you have it for every year?
/Imke
Bless your heart for responding on a Saturday evening. BF in Jan for every year. ytd gives closing balance for each month.Then I can have each year contained. I had the idea of creating a table with summarise to put Ytd values in and then doing averaging from that. What do you think?. and how do you do it?.
Thanks
Pete