Home » DAX » Power BI – Change display unit based on values in table

Power BI – Change display unit based on values in table

Custom Display units in Power BI

Power BI Desktop provides you to some default Display units to change the values format in visuals, but to using some Dax functions you can create a custom display units.

Default Display Units format:

Select visual > Go to format bar > turn on data labels > select display units

Display Units Power Bi

Display Units Power Bi

Now the question is how you can create a custom display units?

So, Let’s start with an example, download the sample Dataset from below link

Step-1: For this we will use DAX function, let’s assume you want to see values in thousand so for this create a measure with some dax functions as mentioned below.

Sale in (K) =
Var TotalSale = SUM('Global-Superstore'[Sales])
Var FinalSale = DIVIDE(TotalSale, 1000)
Var decimals = "0.0"
FORMAT ( FinalSale, decimals & "K" )

DAX Description:
  • SUM: It will return total sum of sales
  • Divide: To get Sales in thousand divide total sum of sales with 1000.
  • Format: Converts a value to text according to the specified format.

Step-2: Now drag measure to table visual to see the output

Custom Display Units 1

Custom Display Units 1

Note: This measure will support only table and Matrix visuals, because here we are adding numeric and string value together, so other charts will not support this measure as in value.

Now create a custom display units based on some conditions:

Create a measure with some condition as mentioned below-

Custom Display unit =
Var TotalSale = SUM('Global-Superstore'[Sales])
Var decimals = "0.0"
TotalSale > 1000000 , FORMAT ( TotalSale / 1000000 , decimals & "M" ) ,
TotalSale > 100000, FORMAT ( TotalSale / 100000 , decimals & "L" ) ,
TotalSale > 1000 , FORMAT ( TotalSale / 1000 , decimals & "K" ) ,
FORMAT ( TotalSale , decimals )

Now drag measure to table visual and see the output-

Custom Display Units 2

Custom Display Units 2

Recommended Post: SWITCH DAX

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Leave a Reply