Formatting numbers in a visual based on its value

[Chandan3]

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

[v-stephen-msft]

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.

[Chandan3]

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.

[Chandan3 · Accepted solution]

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)

)