Data to sum not recognized

[StefanoS]

Hi All!

I've tried to search a solution within the forum but I did't find a similar case, apologize in case I'm duplicating the topic.

In my Excel dataset used as source for Power Bi I created a new colum (call it column A) where I wrote a formula (IF).

The formula is referring to another column (call it column B) in the same sheet and both column are formatted as "Accounting" in $.

When I refreshed my Power Bi I found the new column (A) in Power Bi Fields but when I tried to sum the values it doesn't work (I used a "Card" visualization).

With the "Card" visualization I can only select: "First", "Last", "Count" and "Count (Distinct)"

With another visualizations (i.e. "Table") appears also "Don't summarize" in addition to the previous calculations.

I noticed that new Field (A) hasn't the grey summation sign before the name, while the source column (B) has it.

I tried:

- to change the A column's format in number

- to copy and paste as value the formula in column A

- to change the A column title

- to format the column A equal to column B

- to create a new measure as: =sum(Data(column A))

but all without success... 😞 

I would like that column A works exactly as column B, how can I solve this issue?

Thanks a lot in advance.

Stefano.

[v-jiascu-msft]

Hi @StefanoS,

Please share your pbix file if possible. At least a sample and some snapshots.

Best Regards!

Dale

[StefanoS]

Hi Dale @v-jiascu-msft,

thanks for your reply and apologize for my answer's delay.

referring to my previous message Column "A" is the column AQ in the first picture below (PowerBi source file), Column "B" is column AG.

Column AG is part of original file, column AQ is created with a formula linked to AG (a simple percentage), I tried either to keep AQ as formula and to copy and paste it as value, but no results.

Basically I want PowerBi treats AQ as AG. As you can see from the pictures 2 and 3 below PowerBi sums column AG (picture 2) but it doesn't do the same with column AQ (picture 3), it shows only "First", "Last", "Count" and "Count Distinct" as options.

Even the column AC in source file (quantity), which is not formatted as accounting, is summarized in PowerBi with the same visualization (Card) and it's the card showing "30K".

All the 3 columns in source file (AC, AG and AQ) have some "blanks" or zero value for some rows.

How is it possible I cannot sum the column AQ in Power Bi?

thank you in advance.

123

[KHorseman]

Again, these are the options you get for columns that are recognized as text. There is probably one row in AG that has non-numeric data in it, so when you load it into Power BI the entire column gets treated as text. It's all or nothing. If even a single row cannot be recognized as a numeric value or null, Power BI cannot treat that column as a number. Screenshots are of little use here if you can't find the problem on your own. We would need to see the source excel file at least, preferably also the pbix file as well.

[KHorseman]

That column must be text. If your source is excel, at least one row must have an extra character or something. Maybe a space. Go to the query editor and try adding a step using the Trim function to remove extra characters, then add a step explicitly setting the data type to number.