Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SnGad
New Member

Changing column format according to value

Hi,

I have created unpivot column 'Value' of two columns - Amount and Count in power BI.

Measurement    Value

Amount             135

Amount             1000

Count                 1

.

.

.

I want to change the formatting of the column value depending upon if it is amount or count.

For example, 135 should be displayed as $135 and count should be just a whole number. 

Are there any ways to achieve this?

 

1 ACCEPTED SOLUTION
moumipanja
Employee
Employee

You can not have different datatype or format for a single column. However, if you want just the currency sign to be displayed for Measurement = "Amount", then you can try the following steps:

 

1. Change the datatype of your Value column to Text

1.JPG

 

 

2. Then create a new calculated column using the following DAX:

Column2 = IF(Sheet2[Measurement]="Amount", ( FORMAT ( CURRENCY ( Sheet2[Value] ), "Currency" )), Sheet2[Value] )
1.JPG
 
3. You can use other DAX queries as well to concatenate a $ symbol with Value when Measure = "Amount" 
Column 3 = IF(Sheet2[Measurement]="Amount", CONCATENATE("$",Sheet2[Value]), Sheet2[Value])
 
1.JPG
 
Let me know if this works for you.

View solution in original post

1 REPLY 1
moumipanja
Employee
Employee

You can not have different datatype or format for a single column. However, if you want just the currency sign to be displayed for Measurement = "Amount", then you can try the following steps:

 

1. Change the datatype of your Value column to Text

1.JPG

 

 

2. Then create a new calculated column using the following DAX:

Column2 = IF(Sheet2[Measurement]="Amount", ( FORMAT ( CURRENCY ( Sheet2[Value] ), "Currency" )), Sheet2[Value] )
1.JPG
 
3. You can use other DAX queries as well to concatenate a $ symbol with Value when Measure = "Amount" 
Column 3 = IF(Sheet2[Measurement]="Amount", CONCATENATE("$",Sheet2[Value]), Sheet2[Value])
 
1.JPG
 
Let me know if this works for you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.