Handle formatting with multiple data type in same column

[apradh6]

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!

[Kumail]

Hello @apradh6 

Hope the below measure helps.

FormatDataType =

VAR Val = SUM ( 'Table'[Activity Progress] )

RETURN

SWITCH (

SELECTEDVALUE ( 'Table'[DataType] ),

"Dollar", FORMAT ( Val, "$0" ),

"WholeNumb", FORMAT ( Val, "0.00" )

If you have any other queries, you can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

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]

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]

[ibarrau]

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,