Forum Discussion
For a Matrix, how do I apply Numeric Format with commas on a Count?
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.
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.
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?
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.
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.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...