Numeric Fields Appearing as Text in Excel PivotTable

[AnnaBasova]

Hi guys!

I'm new in Power BI, so I apologize if my question is trivial or stupid )))

I have a semantic model published in Power BI Service. The model contains tables with numerical measures, which are correctly recognized as numbers throughout the entire model (verified in table view in Power BI Desktop, in Power Query, and Power BI Service - everywhere they are treated as numbers).

However, when I create a report in Excel using "analyze in excel" from Power BI Service, all numerical values in the PivotTable fields appear as text value.

This is not an isolated issue. This is a problem with all my semantic models. Every report based on any of my semantic models has this problem.

I have already tried:

Recreating the report from scratch
Clearing the Excel cache
Neither solution worked ((( all values ​​are still displayed as text, without preserving the numeric format from the semantic model.

What can I do to fix this? Help please!

[v-karpurapud]

Hello @AnnaBasova 

Hope you are doing well! Glad to hear you found the cause and resolved the issue! Please mark your insights as "Accept as Solution" to help others.

Thank you.

[v-karpurapud]

Hello @AnnaBasova 

Thank you @arvindsingh802 for your prompt response.

Thank you for reaching out to the Microsoft Fabric community. We understand you are experiencing an issue with Excel PivotTables.

This issue arises because Excel treats numeric measures from Power BI datasets as text by default in PivotTables when using "Analyze in Excel." There is currently no automatic fix. Please follow the method below:

Explicitly format the measure in Power BI Desktop by setting the appropriate number format in the Model view under Properties, then Save & Publish the model to power bi service to ensure Excel recognizes it correctly. For best results, always format your measures in Power BI before publishing and additionally try to  manually adjust the number format in excel when needed, as recommended by Super user.

 

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you!

[AnnaBasova · Accepted solution]

Hello! Thank you for your answer. I solved the problem by creating a calculated measure in Power BI. Unfortunately, simply using the numeric format does not help. It is displayed in the desktop version, in Power BI service, but in Excel it is still text. But the calculated measure is OK. It works. I solved the problem like this. If that is what you meant, then it works.

[v-karpurapud]

Hi  @AnnaBasova 

We are pleased to hear that you have found the cause and resolved the issue. Thanks for sharing the details here.  Please mark your insights 'Accept as solution' to help others with similar problems find it easily. 

Thank you.

[v-karpurapud]

Hello @AnnaBasova 

Glad to hear that your issue is resolved! Kindly accept the solution so that it can help others easily find and apply the fix.

Regards,
Karpurapu D.

[arvindsingh802]

You can update the format to numeric in excel and save that copy, it will preserv your formatting after refresh as well