Handle formatting with multiple data type in same column

Question

Hi,&nbsp;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.&nbsp;&nbsp;Activity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Activity ProgressActivity 1 Count&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5Activity 1 Dollar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100Activity 2 Count&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10Activity 2 Dollar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1000&nbsp;Thanks!

kumail · Answer

Hello&nbsp;apradh6&nbsp;&nbsp;Hope the below measure helps.&nbsp;FormatDataType = VAR Val = SUM ( 'Table'[Activity Progress] )RETURNSWITCH (SELECTEDVALUE ( 'Table'[DataType] ),"Dollar", FORMAT ( Val, "$0" ),"WholeNumb", FORMAT ( Val, "0.00" )&nbsp;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.&nbsp;&nbsp;RegardsKumail RazaDid this help? Kudos are appreciated.Consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

ibarrau · Answer

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.&nbsp;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 · Answer

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.&nbsp;&nbsp;&nbsp;

apradh6 · Answer

ibarrau · Answer

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 $)&nbsp;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)&nbsp;Regards,

Forum Discussion

Handle formatting with multiple data type in same column

5 Replies

Related content

Handling Null Values

Handling multiple dates with one date picker in semantic model

Handling two date formats

Handling schema changes and multiple schemas in Eventstream

Conditional formatting using multiple calculation groups