cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chandan3
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

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)
)

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
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")

3.png

 

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.

 

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.

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)
)

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors