Dynamic scaling of a measure in Power BI with DAX

This post is also available in: Deutsch

Automatic scaling of metrics or measures is possible in Power BI for just about any visual. However, there is currently no possibility to dynamically adjust tables or the matrix.

No dynamic scaling option for table and matrix

With this selection the values can be displayed in millions, for example.

Even if the values are restricted due to a slicer, filter or drill down, the manually set scaling is kept. Depending on the constellation, this leads to the strange situation that no more information can be obtained from the report because the values are too low for scaling.

Low turnover due to filtering by customer

While this is handled in a graphical visual and the scaling is adjusted, this option is unfortunately not available in a table or matrix.

Dynamic scaling with DAX

However, you can influence the formatting directly in a DAX measure so that it automatically adapts to the displayed value. The FORMATfunction will help us here. This function is similar to the formatting in Excel and only allows us to adjust the visualization, the values remain unchanged.

The following syntax would display the value as a normal number:

FORMAT( SUM( FactInternetSales[SalesAmount] ) , “€ 0.00” )

The documentation for custom formats shows us that a comma helps to “shrink” the displayed number in blocks of thousands. In a nutshell, each comma shows 3 digits less.
So the representation in thousands would be for our measure:

FORMAT( SUM( FactInternetSales[SalesAmount] ) , “€##0,.00 Tsd” )

To get the representation in millions an additional comma is sufficient:

FORMAT( SUM( FactInternetSales[SalesAmount] ) , “€##0,,.00 Mil” )

Which scaling should be displayed we have to define in the measure. Here we calculate whether the given number can be divided by 1,000, 1,000,000 or 1,000,000,000 and then return the value in the appropriate formatting.
This can be done by nested IF functions, but I personally prefer a SWITCH(TRUE() for clarity reasons.

SUM Sales dynamic = VAR _SumSales = SUM( FactInternetSales[SalesAmount] ) VAR _FormattedDate = SWITCH( TRUE(), _SumSales / 1000000000 >= 1, FORMAT( _SumSales, “€ ##0,,,.00 Mrd” ), _SumSales / 1000000 >= 1, FORMAT( _SumSales, “€ ##0,,.00 Mil” ), _SumSales / 1000 >= 1, FORMAT( _SumSales, “€ ##0,.00 Tsd” ), IF( ISBLANK( _SumSales ), BLANK(), FORMAT( _SumSales, “€ 0.00” ) ) ) RETURN _FormattedDate

When we now display this measure in our table, the scaling is automatically adjusted. No matter if we have the overall view:

And also when we filter on individual customers:

However, the approach still has 2 disadvantages:

  1. Since the FORMATfunction returns the value as text, the measure can unfortunately not be displayed in graphics.
  2. As already mentioned, since it is text, the decimal places can unfortunately not be adjusted dynamically.
Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and senior consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x