Koen Verbeeck on 14 Apr 2016 17:42:51
Power BI (Desktop) supports formatting designed in the SSAS cube. However, when I format a measure as \€ #,##0.00;\€ -#,##0.00, the € sign is lost in Power BI. It works in Excel though.
- Comments (37)
RE: Better support for SSAS MD formatting
I fully agree with the comment below. Please please Power BI Team, fix this inconsistency:
"Slightly related: we have a calculated % measure in a multidimensional cube with format set to "Percent". When displayed on a data table in Power BI (we're using a LIVE connection) it CORRECTLY displays with a % format (e.g. 20%), however when that SAME measure is placed on a Chart control the tooltip and the Y-Axis INCORRECTLY display and show this as a numeric (i.e. "0.2" instead of "20%").
The fact that the calculated measure displays using the CORRECT format in the Power BI data table but then INCORRECTLY when switched to a Chart visualization is inconsistent and obviously not by design. This also shows that the format for this calculated cube measure is obtained by Power BI, but it is not being consistently applied across the visualizations.
The Table & Matrix visualization display the % with correct format, but I haven't found any other visualization that does..
This is a show-stopper for rolling out Power BI to our target audience (Execs, etc.)"
RE: Better support for SSAS MD formatting
Slightly related: we have a calculated % measure in a multidimensional cube with format set to "Percent". When displayed on a data table in Power BI (we're using a LIVE connection) it CORRECTLY displays with a % format (e.g. 20%), however when that SAME measure is placed on a Chart control the tooltip and the Y-Axis INCORRECTLY display and show this as a numeric (i.e. "0.2" instead of "20%").
The fact that the calculated measure displays using the CORRECT format in the Power BI data table but then INCORRECTLY when switched to a Chart visualisation is inconsistent and obviously not by design. This also shows that the format for this calculated cube measure is obtained by Power BI, but it is not being consistently applied across the visualisations.
The Table & Matrix visualisation display the % with correct format, but I haven't found any other visualisation that does..
This is a show-stopper for rolling out Power BI to our target audience (Execs, etc.)
RE: Better support for SSAS MD formatting
also, if you add ;;0 at the end of the format to display null values as zero this doesn't work. This an issue in direct query mode as you can't do any formatting in Power BI. You just have to accept what comes back from the cube which means null values will always be blank. Using import mode you can create a measure which does it in Power BI but I can't import becasue my data set is too large.
RE: Better support for SSAS MD formatting
This is also true with Dim fields. My DIM fields are rounding to two decimal places despite the cube format having many more.
RE: Better support for SSAS MD formatting
The fact that this isn't fully supported by now is downright absurd. How on earth does Microsoft think anyone would adopt their product if simple format stings cannot be rendered?
RE: Better support for SSAS MD formatting
This is also a problem for SSAS Tabular
RE: Better support for SSAS MD formatting
Since formatting from SSAS MD cubes does not come through on stored calculated members I'm trying to get around this by building calculated members. For example:
CREATE MEMBER CURRENTCUBE.[Measures].[Units Sold %]
AS
CASE
WHEN [Scenario].[Scenario].CurrentMember = [Scenario].[Scenario].&[2017 Actual+Orders vs 2017 Budget]
THEN
DIVIDE(([Scenario].&[2017 Actual+Orders vs 2017 Budget],[Measures].[Units Sold])
,([Time].[Year Nr].&[2017],[Scenario].[Scenario].&[Budget],[Measures].[Units Sold]))
ELSE NULL END
------------------------------------------
,FORMAT_STRING = "Percent"
,VISIBLE = 1
,ASSOCIATED_MEASURE_GROUP = 'Measures'
This returns a value that is formatted as a percrent but then I get more columns than I want such as "Units Sold %" under scenarios not referenced in the calc above. I tried switching off the automatic column width and hiding the column but then the table doesn't refresh.
Is there a way I don't know about that will enable me to hide the extra columns with no values?
RE: Better support for SSAS MD formatting
We are starting to look at SSAS with Power BI for some of our BI needs. Its extremely important that we can inherit the formatting as specified using the formatting strings in the SSAS measure design.
Please work on incorporating this into an upcoming release of Power BI.
I'll try using the workaround as suggested by Chris Webb
RE: Better support for SSAS MD formatting
I'm really surprised one this one. It's going to look pretty unprofessional not to be able to show a percentage value as a decimal. On premise SSAS Customers are obviously taking a back seat.
RE: Better support for SSAS MD formatting
Best work around I've found. And from the infamous Chris Webb. Trying his workaround now.