Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LukeB
Frequent Visitor

Percentage formatting issues when exporting to excel in service

I have a PowerBI Dataset which my report connects to. I have created a measure in my report which is a simple Divide([Columnx]/[ColumnY]). I have set the formatting of this measure to percentage with 2 decimal places. This works perfectly fine in the visual and shows how it should as below. Also if I exporting the tablix in the PBIX file all formatting is remembered.

LukeB_1-1677600329716.png

 

This is the format of the measure

LukeB_3-1677600457476.png

 

However when I publish up to the service and then export the tablix into excel the % changes to a decimal number.

LukeB_0-1677600240861.png

 

I imagine this is because the data type in the above screenshot is set to decimal number. However there is no option to select percentage in the data type list. I could always wrap the measure in a FORMAT but this seems like overkill for something that shouldn't happen.

Can anyone please help?

1 ACCEPTED SOLUTION

Hi , @LukeB 

 I asked the product group about this, and the response from the product group is as follows:

This is By Design. Measures created on top of a Live Connect Dataset has their formats on the client side. This is considered client side formatted and is not included in Export. 
 
Depending on your needs, you can try the workaround described above as a workaround.
 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

Hi , @LukeB 

I test in my side , otherwise i use the calcualted column or the measure , Exporting excel works well(Only the "Exporting to excel with live connection has a bug now").

vyueyunzhmsft_3-1677721924405.png

vyueyunzhmsft_4-1677721932476.png

 And this is the Exporting to excel with live connection.

vyueyunzhmsft_6-1677722159598.png

 

vyueyunzhmsft_5-1677722053145.png

This issue has been submitted to the product group for study and fixing.You can try to use this mode to export excel or use other workrounds.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hello Thanks for the response

Yes this is the same for me - when connecting to an existing dataset in the Power BI Service this is the tablix (screenshot below) I have. The columns with numbers in are measures that live inside the existing dataset. The % is a measure I have created in the report which connects to the dataset.

This is how the tablix looks in the visual

LukeB_0-1677749033931.png

Soon as I hit export to excel (data with current layout or summarized data) in the service the formatting is lost:

LukeB_1-1677749199580.png

As you can see below:

LukeB_2-1677749374930.png

Thank you for raising the issue - if you could update me on if a fix will be applied that will be great?

Hi , @LukeB 

 I asked the product group about this, and the response from the product group is as follows:

This is By Design. Measures created on top of a Live Connect Dataset has their formats on the client side. This is considered client side formatted and is not included in Export. 
 
Depending on your needs, you can try the workaround described above as a workaround.
 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Hey @v-yueyunzh-msft 

Thanks for coming back to me, really appreciate it. Ok that seems a little strange that formatting isn't available on excel export with measures created over a live connect dataset.

I'll use the workaround options for now

 

Thanks

 

Luke

Hi , @LukeB 

According to your description, when you connect to a dataset and add a measure ,then publish it to the Service . I test in my side , the result is the same as yours.And i will post this issue as soon as poosible today.

vyueyunzhmsft_0-1677806784161.png

For this is a issue now , you can try to export the data in Power BI Desktop as a workaround:

vyueyunzhmsft_1-1677806828349.png

vyueyunzhmsft_2-1677806836975.png

Method 2nd , you can use FORMAT() function in the measure and republish it :

vyueyunzhmsft_3-1677806855493.png

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors