For a Matrix, how do I apply Numeric Format with commas on a Count?

[LukeChung]

For a matrix, I have the Rows and Columns fields, with a Count of a field as the Values.

I can't seem to apply a numeric format on the grid for the values in thousands to be displayed with commas. Where is this set?

By selecting the Matrix, there doesn't seem to be a way to get the Format ribbon to show the numeric format section. No matter what I select on the Visualization Panel, it doesn't appear. I was expecting it would be on the Field Formatting section, but it doesn't appear.

I tried to apply the format by clicking on the matrix, then the Fields pane on the right and selecting the numeric field I'm counting on, but that doesn't make a difference. Nor should it since the formatting should be on the Count, and not the original field.

Any suggestions? We have an older matrix that shows its thousands with commas but I can't figure out how it was customized. Thanks.

[LukeChung]

Thank you for your suggestion. Do I need to create a Measure first? I just dropped the field into the Matrix's Values box and use the Count option in the drop down. Does that not allow numeric formatting?

If I create a Measure for the original source, do I need to aggregate there? Currently, the matrix is doing the calculation so the underlying data relationships persist. Clicking on any cell in the matrix automatically filters the other panels.

FYI, I'm using the Power BI Windows Desktop version. 2.86.902.0 64-bit October 2020. Do I need to upgrade and if so how? I assumed it would upgrade itself.

[KNP]

I don't think you can format a field dropped in like that for counting.

Yes, create the measure and drop that in the matrix in place of the field. No additional aggregation should be required if the data model has appropriate relationships.

If you use the Microsoft Store version, it is self updating, the other version needs to be done manually. What I've suggested doesn't require you to update.

[LukeChung]

So what kind of measure do I create? NewField=1 (or any number), then format that, and drop that in the UI for it to count? Or sum?

Or does the Measure need to be a Count of the PrimayKey (so it becomes a 1), formatted with commas, then inserted into the UI using a sum?

Not clear what I'm trying to create with the Measure. Is it just a value like 1 or do I need to reference an existing field?

What does the Microsoft store version mean? Is the Windows Desktop not a Microsoft store product? Should I be using another version of Power BI?

[KNP · Accepted solution]

Because I don't know your data you'll need to change the below example as required.

After you right click on your table and select 'New Measure'

Enter your mesure...

MyCount = COUNT(MyTable[MyColumn])

Replace MyCount with whatever you want your measure to be called. Replace MyTable and MyColumn with your table and any column but ideally something that doesn't have blanks or nulls. So yes, as you mentioned, something like a primary key would be ideal.

Once you've formatted as you want it and drop it in your visual, no further aggregation is specified. That is implicitly handled by whatever level your visual is grouping to.

Power BI Desktop has two options when downloading...

There are not a lot of differences, not sure why they both exist. Maybe because some companies disable the MS Store through group policies to prevent other downloads. 

If you download the version from the store, you don't have to worry about updating. Some people prefer to choose when they update. Either is fine.

[LukeChung]

Thanks! That worked.

A very unintuitive way to set numeric formatting which should be on the grid, not a separate copy of a field for every different uses. It is what it is, so thanks for explaining it. I doubt I would have figured this out on my own.

[KNP]

Glad I could help.

Just as an aside, if you were doing anything else other than a count, on a numeric column, e.g. sum or average, PBI will respect the formatting you have set for that column.

The thing with the count is, it doesn't necessarily have a base format to work with, you could be counting text strings or numbers etc.

Generally, creating measures is the best way for everything, and would be required if you ever wanted to consume the PBI dataset in excel.

Column formatting is typically set here...

[KNP]

Hi @LukeChung,

I'm not sure you can format a field that you are counting like that.

Easiest way is to create a measure and use that instead. 

MyCount = COUNT(MyTable[MyColumn])

That way, you'll get the normal formatting options for the measure.

[lbendlin]

Needs more details - are you using the old style pane or the new preview of the properties pane?  Options have moved around a lot.