Format a summary value on a currency field

[pkoel-asb]

Hello,

  I have a currency field names OrderTotal. This is set as a currency field with 2 decimal positions. When I use teh SUM summary function on the field, it correctly formats the number with a $ and commas as expected. When I use the COUNT summary function; It will count the transactions, but does not put a comma in the thousands and millions positions. I get 59354 rather than the expected 59,354.  I do not know how to format the summary function to show the comma. Any suggestions?

Cheers,

Peter

[v-yuaj-msft]

Hi @pkoel-asb ,

Based on your description, I did a test. You could create a measure instead of changing aggregated type of the field.

count_CC = DISTINCTCOUNT('Table_count'[Date])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[v-yuaj-msft]

Hi @pkoel-asb ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Yuna

[Sumanth_23 · Accepted solution]

hi @pkoel-asb - you can apply the thousand seperator formatting after selecting the measure or calculated column using the setting shown in the below screenshot

Hope that helps! 

[pkoel-asb]

Thank you for the fast responce.I am using the summary functions that are done on the field names. 

Peter

[Sumanth_23]

hi @pkoel-asb - Just to confirm - are you are using the summarizing the "Value of Orders" field to get the "Order Count"? 

Would it be possible to share a sample pbix file for me to better understand how the fields are setup. Thanks! 

[pkoel-asb]

Hello,

Let me rephrase the issue. I am unable to provide the requested file. I have a Customer table and an Orders table. The two tables have a 1:M relationship based on the customer ID. I want to count the number of orders that each customer has placed.  Here is a screen imange of my desktop:

  You can see the field is formatted as currency and I have a ',' specified. What I do not understand is why the total count does not have a comma in the value 562192. If you look at the values under Order Count, they too are missing the ','

It should be 562,192. 

Sincerely,

Peter

[Sumanth_23]

hi @pkoel-asb - Thanks for the detailed explanation on what you are trying to implement. 

Unfortunately there is no setting to format the totals when you use the summarize (sum, average, count etc.) - you can try using the order number (or another) field in your Order table and format that field itself to display with the thousand seperator. 

A better alternate is to create a DAX to calculate the count of orders and format the same. 

OrderCnt = COUNT( Orders[OrderID] )