How to get the Total of Column

[Divya904]

Hi Experts 

Please help me in this.

I have a column in my dataset named Amount. Which is of text type. It has values 55AUD , 130 AUD,  250 AUD.

Can we get the total of this column i.e. 435AUD.

Thanks

[rohitMe]

Hi @Divya904

I would suggest you to first convert the data type of your column to decimal number and change its format from the modelling tab to currency (using $ English(Australia)) as your format. Then you can create a measure to calculate the total of the column and use as you like it.

Hope this helps you to sort your problem.

Regards

Rohit

[Divya904]

Hi @rohitMe

Thanks for your reply.

I have tried to change the datatype of this column from text to decimal in Power BI query editor. It gives an error. Please advise on it.

Thanks.

[rohitMe]

Hi @Divya904

Well since your column has both text and numbers you will have to split the column as @Ashish_Mathur has mentioned. Split the column by using the split by delimiter function in query editor of power bi and specify the delimiter as "Space".

Then you can follow the method that I had suggested to acheive the required result.

First convert the data type of your column to decimal number and change its format from the modelling tab to currency (using $ English(Australia)) as your format. Then you can create a measure to calculate the total of the column and use as you like it.

Regards

Rohit

[Divya904]

Hi @rohitMe and @Ashish_Mathur

Thanks for helping me. I cann't split this column beacuse users want values like this for eg. 150AUD . Just wondering if we can summarize it as it is.

Thanks and Regards

Divya

[Divya904]

Hi Rohit and @Ashish_Mathur

Thanks for helping me. I cann't split this column beacuse users want values like this for eg. 150AUD . Just wondering if we can summarize it as it is.

Thanks and Regards

Divya

[rohitMe]

Hi @Divya904

How do you want to show your total value as? Should it look like 150 AUD or it can look like $150.

Regards

Rohit

[Divya904]

Hi Rohit

Thanks for your prompt reply.

Values in Columns are like  150AUD, 200AUD, 300AUD and total can be either in the form of $650 or 650AUD. 

[rohitMe]

@Divya904

Try this out then:

In query editor Select your Amount column and click on Extract button in Add column Tab. Use the text before delimiter option and specify delimiter as "Space". Then change the data type to decimal and format to the required currency and make a measure to calculate the total for the amount(Or you can directly use it in your visualisation just confirm that its value is shown as sum of the column and not as count or something else).

This should give you the total as $150 or if you do not want the dollar sign then do not change the format keep it in general it will show as 150 only.

Note: You can hide the Extra column by using the "Hide in Report view" option such that it does not show in your report, but only use this if you are creating a measure to calculate the total not if you are using the column directly in your visuals.

Hope this helps.

Regards

Rohit

[Ashish_Mathur]

Hi,

In the Query Editor, use the Split column feature to split the string into a column of numbers and text entries.  You can then write a measure to add up the numbers.