cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lucian Regular Visitor
Regular Visitor

Cannot format numeric value in data card visualization

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

Hi @Lucian,

 

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

Cannot-format-numeric-value-in-data-card-visualization

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-jiascu-msft Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

Hi @Lucian,

 

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

Cannot-format-numeric-value-in-data-card-visualization

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Lucian Regular Visitor
Regular Visitor

Re: Cannot format numeric value in data card visualization

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 Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lucian Regular Visitor
Regular Visitor

Re: Cannot format numeric value in data card visualization

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.

 

BI-Values.png

 

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 Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
v-jiascu-msft Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lucian Regular Visitor
Regular Visitor

Re: Cannot format numeric value in data card visualization

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 Super Contributor
Super Contributor

Re: Cannot format numeric value in data card visualization

Hi Lucian,

 

I would suggest you vote up this idea.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lucian Regular Visitor
Regular Visitor

Re: Cannot format numeric value in data card visualization

Hi Dale!

 

Thank you for your suggestion. 🙂

 

Kind Regards,

Lucian

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 249 members 2,859 guests
Please welcome our newest community members: