cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apradh6 Frequent Visitor
Frequent Visitor

Handle formatting with multiple data type in same column

Hi, 

My data has a column which has both the count and dollar amounts. Is it possible to preserve this in a table in power bi? I want to show the dollar amount in currency format and count in decimal. I prefer not to separate them out in different columns as there are multiple categories of counts and currencies. 

 

Activity                                Activity Progress

Activity 1 Count                               5

Activity 1 Dollar                             $100

Activity 2 Count                               10

Activity 2 Dollar                            $1000

 

Thanks!

4 REPLIES 4
ibarrau Senior Member
Senior Member

Re: Handle formatting with multiple data type in same column

Hi. If I understood you will be able to do this with measures. First you need a column to identify when is it a dollar value and when is it a count value. If you have that, you can create a measure like:

Money = 
CALCULATE (
    Table[Activity Progress] ,
    Table[ColumnToIdentify] = "Dollar"
)

Then change the format to that particular Measure. Add a new measure for Activity count and format it as you want.

 

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

apradh6 Frequent Visitor
Frequent Visitor

Re: Handle formatting with multiple data type in same column

Hi ibarrau,

The data is in an excel file (sample below) and when I use Get data to import it to PowerBi it changes everything to a single format. I am not sure about your response before. Can you elaborate? I am new to Power BI and I apologize if I sound like an amateur. I want to show it similar to the file in a "Table" visualization with the correct formatting. 

 

 

apradh6 Frequent Visitor
Frequent Visitor

Re: Handle formatting with multiple data type in same column

Capture.PNG

ibarrau Senior Member
Senior Member

Re: Handle formatting with multiple data type in same column

Ok. You won't be able to show two different formats on the same column table.

1- What you can do is split the excel in two tables (in query editor getting the file two times and filtering by # or $)

2- The other suggestion is loading the complete excel specifing Progress as Decimal number. Apply changes. Then you can create a Calculated Measure in DAX to get two values: Money Progress and Count Progress (as I mention before, you may use containsstring function to ask for # or $)

 

The picture you upload won't be a posible visualization. You will be able to show them in correct format in two columns (one for each table solution1 or measure solution2)

 

Regards,

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)