Percent display issue in charts in olap live connection

[Krish]

Hi Team -- We have a measure in SSAS OLAP and it is formated as "0%;(0%)". It shows correctly in Excel pivot tables and even cube values. However, in PowerBI Live connection, it shows as decimal number.

FYI... I tried changing the format to "Percent" still no use.

Thanks

Krish

[mchapman]

I tried using "Percent" as well and no luck when applying format like this:

FORMAT_STRING([Measures].[Gross Margin]) ="Percent";

However, it did work when I added it to a created memeber like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Gross Margin Percent] AS

DIVIDE([Measures].[Gross Margin],[Measures].[Net Sales]),

VISIBLE = 1,

ASSOCIATED_MEASURE_GROUP = 'Measures'

, FORMAT_STRING="Percent";

I would also like to note that I was in a shared workgroup and had to delete and re-add the data connection as for it to work as the cube meta data doesn't seem to update when you hit the refresh button.  Anyone know how to make the meta data refresh without having to re-add the connection?

[Anonymous]

This is really @mchapman! To confirm what you said, I was able to show data with a % by formatting the string directly in ssas but since December it does not seem to work anymore. I explained everything here http://community.powerbi.com/t5/Desktop/Formatting-SSAS-values-with-a-Live-connection-WAS-working-un....

Contrary to what has been said, I think it was partially working before December 2016 (as you showed in your example) but as of today it does not work at all anymore.

@mchapman: I'm curious would you be able to try to display your measure above with a live connection SSAS and let me know if it's still working?

Thank you

[mchapman]

@Anonymous I checked via my live connection and it is still working.  I have attached screenshot.

[Anonymous]

Thank you very much, it helps me a lot. I will follow the different steps you described and see if I can get my % working! It seems to be tricky for a lot of people. Thank you!

[Anonymous]

Quick update.

After several attempts, using MDX orthe VS Interface, I can't make that formatting work again. Every single report that was previously showing correctly % are now showing up with no formatting.

I would be glad to be in touch with a PowerBI MS guy.

If any one has an idea please feel free to share it. It seems that more and more people are reporting that issue.

See. http://community.powerbi.com/t5/Desktop/Formatting-SSAS-values-with-a-Live-connection-WAS-working-un...

[Piquet]

We are using Power BI Report Server - March 2018.

We have SSAS 2016 multi-dimensional cubes for analysis and reporting.

We have a calculated % measure in a multi-dimensional cube with FORMAT set to "Percent".

In Power BI (Desktop) with a LIVE connection to this multi-dimensional cube:

• Displaying the measure using a table or matrix visualisation, the measure displays using the CORRECT format (e.g. "20.5%")
• Changing the visualisation to something "more visual" such as a Chart (for example), the Y-Axis and tooltip displays this measure as a numeric, IGNORING the correct Percent formatting (e.g. shows "0.205" instead of "20.5%").

This inconsistency between visualizxations for the same measure (e.g. Table vs. Chart) is obviously not by design.

It is clear that Power BI is obtaining the correct formatting for this measure from the cube (as it is displaying it in the table/matrix visualisations), however it is not being consistently applied across the visualisations. 

This is a show-stopper for rolling out Power BI to our target audience (i.e. Executives, Senior Management, etc.)

[Anonymous]

Same experience to me. I had the visual working with the correct formatting, and after upgrading to CU8 the visual showed decimals afterwards. I wonder, as when I insert a normal table the value is shown correct in the % form. In the queries (I ran a profiler for performance analysis) the query delivers the right format (P2). What‘s wrong there?

[v-haibl-msft]

@Krish

I‘ve got the response from Power BI team. This is currently by design. The work to make this scenario work is known but it was not funded this year. Now I think we can vote this idea here.

Best Regards,

Herbert

[v-yuezhe-msft]

Hi @Krish,

I test the scenario as yours and I can reproduce the same issue as shown in the following screenshots, number format lost after I connect to SSAS using “Connect live” option in Power BI Desktop. I will report the issue internally and post back once I get any update.




Thanks,
Lydia Zhang