I have a problem formatting a number on a Data Card Visualization. The data came from an Access table, where I have a numeric field - "Order number" (in access is an autonumber, so basically is a long integer). Because some orders get deleted I cannot use MAX to find the total orders so I do a count on that field but it shows 8165 instead of using regional settings 8.165. I have checked the formatting tab and tested in Data Label => Display units = None or Auto has no effect.
The "strange" thing is that for another numeric column in the same table - "Total Order Value", using SUM will show 2.601.809,00 - this is partial good, because this card in the Data Label format I cannot change the "Value decimal places" - is greyed out so I cannot change.
To be more stranger, I have exported the table from Access to Excel, blaming on the Access connector on 64bit when I have the entire Office on 32 bit. When import the Excel file in PowerBI the same fields were formatted in opposite form: The COUNT was formatted properly 8.165 but the SUM is 2601809. And from Excel source I could modify the "Value decimal places".
I have updated PowerBi desktop to 2.64.5285.741 64-bit (november 2018) but the problem persist.
So, there is a simple way to put the thousand separator in a Data Card for numeric values?
Thank you for your message, indeed, creating a measure I could use the Modeling tab to format it. But why not formatting directly and have to create a workaround?
The strange fact was for the data taken directly from the Access database, for the field using SUM I could format it directly - selected the field, then I have access to the Modelung tab to format it. But for the field using COUNT, even I could access the modeling settings, it did not have the expected result so I have to create a measure that do the same COUNT (in fact DISTINCTCOUNT) again, but this could be formatted. Why should be different for COUNT/DISTINCTCOUNT?
To be more strange the same data exported from Access to Excel tables I could use the fields with SUM and COUNT formatted directly using the Modeling tab - so no "workaround" measure.
So when data stays in Access, I always have to use a workaround: either move data through Excel, or create measures that do the same calculations?...
Thank you for your message, even though I agree that COUNT may be done over non-numeric columns, as long as the count is over "thousands", I would like to format it properly. Is like more a little bug or an omission from the development team, because as they thought to format it using different units, like thousands, millions and there they format it as 1,5K or 1,5M (with comma) there should be also an 1.500 without the need to create a separate measure for that... ;-)
It would be nice if they would include this as a "feature request" or improvement on the next release... ;-)