Export to Excel - Millions Format not recognized

[Manikantans2]

Greetings,

I am exporting a table chart from Power BI Service. I have formatted my column in Power BI as below by using Dynamic format:

VAR salesMeasure = [Revenue]

return

SWITCH(TRUE(),

salesMeasure<1000,"0",

salesMeasure<1000000, "#,.0K",

salesMeasure<1000000000, "#,,.0M",

salesMeasure>=1000000000, "#,,,.0B")

This column contains some values in thousands and some values in millions. 

When i export to excel, and choose 'Data with current layout', i find that the values in thousands are showing up in my expected format i excel - i.e. as 26.8k for ex. On the other hand, the ones in millions, that show up as 31.9M in Power BI show up as 31931453.432 in excel.

Is there any reason why thousands format gets applied, but millions doesnt in excel ? Any issue with the dynamic formatting expression? And not sure if its related, when i open the excel, it gives a message saying excel found some problem with the content and has repaired or removed unreadable content. 

Regards,

Manikantan S

[SamWiseOwl · Accepted solution]

Hi @Manikantans2 

It appears the formatting for Millions isn't the same as it is in Excel. 0.000,, "M"

Problem is if you use this in Power BI then the data looks awful.

A compromise is manually using Concatenate to add the K, M, T etc

Here someone did it:
https://community.fabric.microsoft.com/t5/Desktop/Raw-Data-in-Thousands-sometimes-in-Millions-How-to...

SWITCH(TRUE(),

     [Revenue]/1000000<1,        CONCATENATE(DIVIDE([Revenue],1000),"K"),

    [Revenue]/1000000<1000,        CONCATENATE(DIVIDE([Revenue],1000000),"M"),

    [Revenue]/1000000<1000000,  CONCATENATE(DIVIDE([Revenue],1000000000),"B"),

    [Revenue]/1000000>=1000000,                CONCATENATE(DIVIDE([Revenue],1000000000000),"T")

   )

[Anonymous · Accepted solution]

Hi, @Manikantans2 

Have you tried the methods offered by SamWiseOwl ? Does his method apply to your situation?

Since you didn't provide a specific test dataset, I created a simple dataset myself to test:

Then create a Measure:

Measure = 
VAR salesMeasure = SUMX('Table','Table'[Revenue])
RETURN
SWITCH(TRUE(),
    salesMeasure/1000000<1,        CONCATENATE(DIVIDE(salesMeasure,1000),"K"),
    salesMeasure/1000000<1000,        CONCATENATE(DIVIDE(salesMeasure,1000000),"M"),
    salesMeasure/1000000<1000000,  CONCATENATE(DIVIDE(salesMeasure,1000000000),"B"),
    salesMeasure/1000000>=1000000,                CONCATENATE(DIVIDE(salesMeasure,1000000000000),"T")
   
   )

Publish to Power BI Service.

Then open the report, click Export data ,select 'Data with current layout' and get the result in Excel file:

I have attached the pbix file of this simple test example below, hope it helps you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manikantans2]

Thanks for the details Fen. This addresses the issue to an extent, in that, the data shows as 1.2M etc in the excel. Only drawback is that, its a string and we cannot sum the numbers in excel. Ideal thing would be for excel to recognize millions, just as it does thousands, which does not seem to be the case. In the absence of that, will take this suggestion as a work around.

Any idea, if this can be raised with Microsoft to ensure format compatibility between excel and power bi?

@SamWiseOwl @Anonymous 

[Anonymous]

Hi, @Manikantans2 

Thanks for your reply.

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

And about your idea, I would suggest you submit your idea at this page . If this feature was mentioned by many users, product team will consider to add this feature to next release.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl · Accepted solution]

Hi @Manikantans2 

It appears the formatting for Millions isn't the same as it is in Excel. 0.000,, "M"

Problem is if you use this in Power BI then the data looks awful.

A compromise is manually using Concatenate to add the K, M, T etc

Here someone did it:
https://community.fabric.microsoft.com/t5/Desktop/Raw-Data-in-Thousands-sometimes-in-Millions-How-to...

SWITCH(TRUE(),

     [Revenue]/1000000<1,        CONCATENATE(DIVIDE([Revenue],1000),"K"),

    [Revenue]/1000000<1000,        CONCATENATE(DIVIDE([Revenue],1000000),"M"),

    [Revenue]/1000000<1000000,  CONCATENATE(DIVIDE([Revenue],1000000000),"B"),

    [Revenue]/1000000>=1000000,                CONCATENATE(DIVIDE([Revenue],1000000000000),"T")

   )

[SaiTejaTalasila]

Hi @Manikantans2 ,

Please refer this -

https://community.fabric.microsoft.com/t5/Custom-Visuals-Ideas/Auto-Format-Numbers-in-Billions-Milli...

You can also try to export the data using power automate(run a query against a dataset) and see whether the same issue occurs or not.

I hope it will be helpful.

Thanks,

Sai Teja