When you are creating measures in your Excel Power Pivot model you can do yourself a great favor if you first set the number format on the column in the data table.
In this example I have a SalesUnits column that is a decimal number and has set the format to General – which is the default.
When I click the Autosum button in the task pane to create a measure or enter the formula manually the number format will also be set to general.
But I want the number format to include a thousand separator and no decimals which means I have to format both measures manually.
If I set the format on the column before I start creating measures – in this case format Sales Units
OBS!!! This only works if you use the AutoSum button.
So if you want to create measures with CALCULATE etc. start by clicking the AutoSum – and then modify the measure and you have the number format in place.
Hope this can help you.