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

Accepted Solutions
moumipanja Regular Visitor
Regular Visitor

Re: Changing column format according to value

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 Regular Visitor
Regular Visitor

Re: Changing column format according to value

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,653 guests
Please welcome our newest community members: