Forum Discussion

Manikantans2's avatar
Manikantans2
Frequent Visitor
1 year ago
Solved

Export to Excel - Millions Format not recognized

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
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    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-quot-Scale/td-p/1883029

    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's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    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-quot-Scale/td-p/1883029

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Manikantans2
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.