Help Formatting Mixed Metrics Table in Power BI (Currency, Percentage, Whole Numbers)

[deepuaits]

Hi Power BI Community,

I'm working with a table that includes a mix of metrics—some are currency values, some are percentages, and others are whole numbers. I want to format each column appropriately in Power BI so that:

• Currency values show with $ and commas (e.g., $55,557,000.00)
• Percentages show with % and one decimal place (e.g., 22.0%)
• Whole numbers show with commas only (e.g., 100,000)

How can I dynamically apply this formatting in Power BI using DAX or Power Query?

Here’s a sample of the table output like below

Thanks,

Pradeep

[Shahid12523 · Accepted solution]

- Currency columns: Format as Currency in Model view → shows $ and commas.
- Percentage columns: Format as Decimal Number + set Format to Percentage → shows 22.0%.
- Whole numbers: Format as Whole Number → shows 100,000.

If you need mixed formatting in one column (like in a matrix), use a DAX measure with SWITCH(TRUE(), ...) and FORMAT() to apply dynamic formatting.

[v-ssriganesh · Accepted solution]

Hello @deepuaits,
I have reproduced your scenario in Power BI Desktop using the sample data structure you shared, and I was able to achieve the expected output where:

• Currency values display with $ sign, commas, and two decimals
• Percentage values display with % and one decimal place
• Whole numbers display with commas only

Here is the sample output I got:

To achieve this, I created a measure using SWITCH() and FORMAT() in DAX to dynamically apply formatting depending on the category type. This way, the table shows mixed formatting in a single column.

For your reference, I am also attaching the .pbix file so you can review and adapt it to your dataset.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh · Accepted solution]

Hello @deepuaits,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @deepuaits, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh · Accepted solution]

Hello @deepuaits,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @deepuaits,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @deepuaits,
I have reproduced your scenario in Power BI Desktop using the sample data structure you shared, and I was able to achieve the expected output where:

• Currency values display with $ sign, commas, and two decimals
• Percentage values display with % and one decimal place
• Whole numbers display with commas only

Here is the sample output I got:

To achieve this, I created a measure using SWITCH() and FORMAT() in DAX to dynamically apply formatting depending on the category type. This way, the table shows mixed formatting in a single column.

For your reference, I am also attaching the .pbix file so you can review and adapt it to your dataset.

Best regards,
Ganesh Singamshetty.

[Shahid12523 · Accepted solution]

- Currency columns: Format as Currency in Model view → shows $ and commas.
- Percentage columns: Format as Decimal Number + set Format to Percentage → shows 22.0%.
- Whole numbers: Format as Whole Number → shows 100,000.

If you need mixed formatting in one column (like in a matrix), use a DAX measure with SWITCH(TRUE(), ...) and FORMAT() to apply dynamic formatting.

[Greg_Deckler]

@deepuaits If those are individual columns or measures then you can format them individually. Otherwise, you need to use the Dynamic Format option if they are all one measure. Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn