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
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: To achieve this, we will utilize a DAX function. Let’s consider a scenario where you wish to display values in thousands.
To accomplish this, you can create a measure employing specific DAX functions, outlined below.
Sale in (K) = Var TotalSale = SUM('Global-Superstore'[Sales]) Var FinalSale = DIVIDE(TotalSale, 1000) Var decimals = "0.0" RETURN 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
Note: This measure will only be compatible with table and matrix visuals. This is due to the fact that it involves the addition of numeric and string values. Consequently, other chart types may not support this measure in terms of value representation.
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" RETURN SWITCH ( TRUE() , 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
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.
Then is there a way to implement the same in charts other than tables and matrix?
No you cannot implement with other visuals.
Now you can implement using the same for any chart using Custom Label.
Go to Data Labels>Values>Custom label. Then add the same measure you created above in the field section.
Hi I try this in matrix + table but the sorting not work.
I guess because there is mix of string and values.
for example:
9M -> 8K -> 4M
Is there a way to apply it?