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(), ...
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.

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.