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.