Cannot format numeric value in data card visualization

[Lucian]

Hello Everyone,

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?

Kind regards,

Lucian

[v-jiascu-msft · Accepted solution]

Hi @Lucian,

We can apply different formats to the values explicitly. Please refer to the snapshot below. 

Best Regards,
Dale

[Lucian]

Hi Dale,

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?... 🙂

Kind Regards,

Lucian

[v-jiascu-msft]

Hi Lucian,

I can't reproduce your scenario that the data from Excel can be formatted. I think this is reasonable. The COUNT of anything is an integer (the Whole number). That could be why we can't add decimals. 

Best Regards,
Dale

[Lucian]

Hi Dale,

And thank you for your message. Well the question was not about houw to add decimals to an integer value... 🙂 

The question was how to add thousand separator to a COUNTed integer value. For more details see the below picture.

To reproduce the picture above, I just put 2 columns into an Excel table ID, VALUE.

The is is just a "serial number" from 1 to 1500 (just fill series with step 1), and the VALUE field are also integer numbers starting from 100 (step 10).

So, the COUNT of ID it cannot be formated with thousand separator even if I go to Modeling tab, but If I create a measure as DISTINCTCOUNT([ID]) it works.

So is it a bug or I have to create a measure everytime I have a COUNTed value which I want to format?

For the SUMed VALUE field I don't have this problem... just for COUNT.

I would attach the simple XLSX file and/or the PBIX but I don't see the option for attaching files, just for pictures.

Kind Regards,

Lucian

[v-jiascu-msft]

Hi Lucian,

I just have the responses from the Product Team. It does work like that. The solution is using a measure. Thank you for reporting it.

Best Regards,
Dale

[Lucian]

Hi Dale,

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

Kind Regards,

Lucian

[v-jiascu-msft]

Hi Lucian,

I would suggest you vote up this idea.

Best Regards,
Dale

[Lucian]

Hi Dale!

Thank you for your suggestion. 🙂

Kind Regards,

Lucian

[v-jiascu-msft]

Hi Lucian,

I can reproduce the same scenario. I'm afraid it could be by design for now. Because the Count function can work with non-numerical columns, it won't inherit the format of the column.

But I still consult the Product Team. I will update here when I get the result.

Best Regards,
Dale