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? 

[Nrcchowdary98]

Hello,

I found a solution that worked for me and might help you as well. Navigate to the Model View tab on the left-hand side of your Power BI report screen. Click on the semantic model to view its properties on the right-hand side, as shown in the picture below. Then, toggle the "Discourage Implicit Measures" button to "No." This should resolve the issue.

If this solution helps, please mark this post as the solution. Thank you!

[tha3]

it does work in my case thanks @Nrcchowdary98 

[Nrcchowdary98]

Hello,

I am experiencing the same issue. I have tried several solutions, but the only thing that worked was rebuilding everything in a new Power BI file. It seems to happen after I create some measures and columns using the data. Any ideas on how to resolve this would be much appreciated. Thanks!

[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.