cancel
Showing results for
Did you mean:
New Member

## Formatting numbers in a visual based on its value

Hi everyone,

I'm using Treemap visual with two levels of data. For a clean report, I want to round the numbers.

In Indian number system, 100,000 (hundred thousand) in international number system is 1,00,000 (one lakh) and 1,000,000 (one million) is 10,00,000 (ten lakh). PowerBI supports the format natively, except in the format section of the visual, the display units are "Thousands", "Millions", etc.

The display unit "Auto" is the correct function I need, but the number system is different. I tried to find a workaround for this without success.

Unlike in spreadsheets, I found that PowerBI does not allow conditions while formatting, like "if amount is bigger than X, apply this format, otherwise apply that format". Measures only show one value, so can't use it inside a visual for this purpose.

Is it possible to format numbers in a visual based on its value? 12,56,524 becomes 12.56 L, but 12,524 becomes 12.52 K?   [L = Lakhs]

If that is not possible, is it possible to format the numbers in a visual to the nearest hundred? 12,56,524 becomes 12,56,500, and 12,524 becomes 12,500?

The numbers itself should not be rounded, it should only be presented as rounded. Because the treemap has multiple levels of data, rounding can change the totals significantly.

Have you faced a similar problem? Do you know if this is solvable, and if it is can you point me in the right direction?

Thank you for your time and have a good day!

Chandan

1 ACCEPTED SOLUTION
New Member

When I answered this I found what I was looking for. I just had to remove the text and add the measure instead.

Amount = VAR YourValue = SUM('Tablename'[Value])
RETURN
SWITCH(TRUE(),
YourValue >=100000, ROUND(YourValue,-3),
ROUND(YourValue,-2)
)
3 REPLIES 3
Community Support

Hi @Chandan3 ,

You can do it with DAX.

Create the following measures:

``````Measure =
VAR YourValue = SUM('Table'[Num])
RETURN
SWITCH(TRUE(),
YourValue >=10000000, FORMAT(YourValue,"##\,##\,##\,##0"),
YourValue >=100000, FORMAT(YourValue,"##\,##\,##0"),
FORMAT(YourValue,"##,##0")
)``````
``````Measure 2 =
VAR YourValue = SUM('Table'[Num2])
RETURN
SWITCH(TRUE(),
YourValue >=100000, ROUNDDOWN(DIVIDE(YourValue,100000),2)&"L",
ROUNDDOWN(DIVIDE(YourValue,1000),2)&"K")``````

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

I want the numbers within the visual to follow this formatting. Is there some way to use measures within a visual?  I don't think is possible because measure with text added is text and not a number.

New Member

When I answered this I found what I was looking for. I just had to remove the text and add the measure instead.

Amount = VAR YourValue = SUM('Tablename'[Value])
RETURN
SWITCH(TRUE(),
YourValue >=100000, ROUND(YourValue,-3),
ROUND(YourValue,-2)
)

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!