Formatting in Power BI for summarized fields using Direct Query

[pmoore]

Hello,

I'm wondering if there is a way to use more precise formatting for values like Count when summarizing a field in a report.  It seems like I have some control over formatting, like using commas, when using Average of a field that I formatted.  However, I don't see a way to use commas, specifically, for the summary (i.e. count) and the number is always displayed as 1234 (for example).

Also, I'm using a card visual that shows average of values.  Since I formatted the underlying number, it does use commas in this case.  However, I don't see a way to get rid of the two decimal places that it shows.

I'm using Direct Query so modeling is limited.  I don't seem to be able to create a measure that uses FORMAT() formula, etc.

Any suggestions?

Thanks!

[jcoffman]

The only way I have found around this was using the FORMAT function.

https://msdn.microsoft.com/query-bi/dax/format-function-dax?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/query-bi/dax/pre-defined-numeric-formats-for-the-format-function

https://msdn.microsoft.com/query-bi/dax/custom-numeric-formats-for-the-format-function

You'll have to create a new column for the calculation though.

e.g.

Closed tickets = FORMAT(COUNTROWS('Closed ticket table'),"00,000")

So, this counts the rows in the closed ticket table and formats it to "00,000"

Was the only way I could get the card count to look like 12,456.

[vanessafvg]

@pmoore

under your modelling table you should be able adust your formatting including decimal points or using a commma

[pmoore]

@vanessafvg,

Correct, that's what I was referring to - I can format the underlying values (if it is a number) and that translates into sum, average, etc.  However, if you do a count, the underlying formatting does not matter and I'm looking for a way to format those count values on the report.

[vanessafvg]

@pmoore How are you doing your count?  Just dragging a text value into a visual and making it count it there?

the best way to handle that is to actually create a count measures i.e countrows(tablename) and then format the measure