Numeric Data Column Cannot Be used as "Values" field in Matrix Table Visual

[LijunChen]

Dear Forum Friends,

I am trying to visualize some pub health data in a Power BI matrix table visual. It is the medicare enrollment numbers for different years and months. But I met with a few difficulties in creating the visual. 1st, many columns/fields are formatted as numeric fields (whole numbers) like "A_B_TOT_BENES", but the data pane does not show these fields as numbers, with no Summation sign. (seen screenshot below).

2nd, I tried to put the column "A_B_Tot_BENES" as Values of the Matrix table, but it won't accept it. 

3rd, but I can create a measure to sum "A_B_Tot_BENES" and put the measure as values in the Matrix table. 

Since the table has many number columns I want to visualize in the matrix table, it will be time consuming to create a measure for each. 

Can anyone help me with the problem? Why the number columns not shown as such? 

Thanks. 

[OwenAuger · Accepted solution]

Hi @LijunChen 

If numeric values can't be dragged into the Values field well, it appears that "Discourage implicit measures" is turned on for this model.

See here:

https://learn.microsoft.com/en-us/power-bi/transform-model/model-explorer#discourage-implicit-measur...

This setting could have either been turned on:

1. Automatically if a calculation group has ever been added to the model.
2. Manually if someone switched this on.

You won't be able to turn this setting off unless the model contains no calculation groups.

If you need to keep calculation groups in the model, then you could use a script like this one in Tabular Editor to create measures aggregating each required column:

https://docs.tabulareditor.com/te2/Useful-script-snippets.html#create-measures-from-columns

Regards

[LijunChen]

Thanks a lot. That answers my question.