Data formatting issue

[EmilieRuff]

Quick question but I have a feeling it might not be possible. 

I am using an excel source document with columns. One column holds the data. The other columns are for filtering purposes. The data has some percentages and others that are decimal numbers. When I then try to add it to a chart for instance and add filters so I can select different categories I then have the issue that either all numbers are percentages or decimals and I can't separate it. I think it will have to be a case of adding it to a new tab in the spreadsheet so the data isn't in the same column. 

But is there another way? 

[grazitti_sapna]

Hi @EmilieRuff 

This is a common challenge when working with mixed data types percentages and decimal numbers in the same column. Power BI interprets the data format based on how the data is loaded and processed, which can cause issues when trying to filter or visualize them together. You can try with following approaches.

1. One straightforward solution is to keep the data combined in the same column but create a new calculated column that standardizes the format.
   • For example:you can add a new column in Power BI with a DAX expression that converts all values to decimals, whether they originally are percentages or decimals.
2. Splitting the data into two separate columns one for percentages, one for regular decimal numbers is a good approach. You can do this in Excel before importing or within Power BI using Power Query by adding custom columns with conditional logic.

Also, while reorganization into a new tab might seem like the easiest solution, creating a calculated column or splitting data with Power Query gives you more flexibility and keeps your dataset cleaner and more manageable.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[WishAskedSooner]

I am not sure I would really recommend this over separating your data, but you could add a conditional format in a measure and use that in your chart e.g.

MyMeasure = IF( 'MyTable'[Attribute] = SomeValue, FORMAT( 'MyTable'[Value], "0.00" ), FORMAT( 'MyTable'[Value], "0%" ) )

[EmilieRuff]

Thank you for your suggestion. I think this is the direction I will investigate.

[pankajnamekar25 · Accepted solution]

Hello @EmilieRuff 

USE DAX for dynamic formatting

Formatted Value =
IF(
SELECTEDVALUE('Table'[Type]) = "Percentage",
FORMAT([Value], "0.00%"),
FORMAT([Value], "0.00")
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[EmilieRuff]

Thank you for your suggestion. I will look into it.