Numeric column would not sum by category in table visual (no sigma sign shown in Fields pane)

[gvg]

Hi folks,

There have been posts about this but I have not found a solution for my case. I have a perfectly numeric field in Power Query, however it does not sum by category when dropped into a Power BI table visual. Its Data type and Format are both set as Decimal number, Summarization is set to Sum, however sigma sign is not appearing at the field's name in Fields pane and the field would not summarize if placed into a table. On the other hand if such field is placed in a measure like Sum(Sales[Amount]) and that measure is placed in a table visual instead, it does summarize as expected. Furthermore, if the whole table is copied from Power Query (Ctrl-C) to a new Power BI instance(Ctrl-V), the field has the sigma sign as expected and works fine. 

Any ideas what can be done to solve this issue? 

[gvg]

Further trying to figure out what is the reason for such stange PBI behaviour:
- tried clearing cash

- deleted all the other tables, measures in the report, leaving just the table that is in concern

Nothing helps.

[ichavarria]

It's possible that the data type of the field is not recognized as numeric by Power BI. To ensure that the field is recognized as numeric, you can try converting it to a number data type using the following steps:

1. Select the column in Power Query.
2. Go to the "Transform" tab in the ribbon.
3. Click on the drop-down arrow next to the data type icon in the "Any Column" group.
4. Select "Number" as the data type.

If the data type is already set to number, you can try changing the data type to a different numeric data type such as "Decimal Number" or "Whole Number" and see if that helps.

If the above steps do not resolve the issue, you can try creating a new column in Power Query using the "Number.FromText" function to convert the text values to numeric values. The formula would look something like this:

= Number.FromText([ColumnName])

Replace "ColumnName" with the name of the column containing the numeric values.

Once you have created the new column, you can replace the original column with the new column in the Power BI report.

Best regards, 

Isaac Chavarria 


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

[gvg]

@ichavarria ,

I tried adding a column by multiplying the column in trouble by 1. It adds the column without any complains but I do not get expected result in the report.