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
dsim089
Regular Visitor

Number format from SSAS cube lost

Hi all,

 

I am using Power BI Desktop to connect to SSAS multidimensional on premis (

Power BI Version: 2.31.4280.361 64-bit (January/2016)

 

I am finding that number formatting configured in the cube is not getting brought into the report. 
For example, I have a Sales measure, with "currency" in the cube. But it just shows as as decimal.

 

I have tried setting the number format to "$#,##0" in the cube, but that makes no difference.

 

Interestingly, calculated measures ARE bringing in the formatting correctly. 

 

Any suggestions? 

 

Thanks in advance.

1 ACCEPTED SOLUTION
dsim089
Regular Visitor

Ok, Microsoft have been investigating this, and have come back with a response.Note that this bug is also present in the AdventureWorks2012 multidimensional cube.

  

The root cause: the DIVIDE() function does not retain the formatting of a measure.

 

Due to our business requiring currency conversion, we had a scope statement performing a division by a month end currency rate. This DIVIDE function was removing the formatting!

 

here's the MDX we were using for our currency conversion:

 

 

Scope ( {
                Measures.[Gross Sales - DRTV]          
                } );                                                                                            
                        Scope( Leaves([Currency Conversion Date]) ,
                                [Reporting Currency].[NZD],     
                                Leaves([Currency]));                                                                                            
                      
                                // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Day Rate] 
                                Scope( {
                                Measures.[Gross Sales - DRTV]
                          } );                                                                                            
                                 
                      This =  Divide([Reporting Currency].[Local],  Measures.[Month End Rate];

 

Here's the response from Microsoft.

-----

"It looks like the MDX function Divide() will cause the result to lose the format. I would suggest that you try the following 

  1. Define a new calculated member as the following:
  2. CREATE MEMBER CURRENTCUBE.[Measures].[Currency Converted]
    AS Divide([Reporting Currency].[Local],  Measures.[Month End Rate]), 
    FORMAT_STRING = "Currency", 
    VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'Fact Currency Rate'  ;
    2. Modify the above “this=” line to the following:

 

This=[Measures].[Currency Converted];    

Since the calculated member is forced to have the format string, the cell should show the value with the currency format in the scopes.

 

The above is the idea on how we can change the MDX script to force the format. You can use the similar workaround on other measures."

 

----

 

I hope this helps anyone else who runs into the same issue.

 

View solution in original post

2 REPLIES 2
koenverbeeck
Advocate II
Advocate II

I have somewhat the same issue, but on regular measures.

I formatted a measure as \€ #,##0.00;\€ -#,##0.00.

Works in Excel, but in Power BI the € is missing. The decimal precision is kept though.

Blog: sqlkover.com
dsim089
Regular Visitor

Ok, Microsoft have been investigating this, and have come back with a response.Note that this bug is also present in the AdventureWorks2012 multidimensional cube.

  

The root cause: the DIVIDE() function does not retain the formatting of a measure.

 

Due to our business requiring currency conversion, we had a scope statement performing a division by a month end currency rate. This DIVIDE function was removing the formatting!

 

here's the MDX we were using for our currency conversion:

 

 

Scope ( {
                Measures.[Gross Sales - DRTV]          
                } );                                                                                            
                        Scope( Leaves([Currency Conversion Date]) ,
                                [Reporting Currency].[NZD],     
                                Leaves([Currency]));                                                                                            
                      
                                // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Day Rate] 
                                Scope( {
                                Measures.[Gross Sales - DRTV]
                          } );                                                                                            
                                 
                      This =  Divide([Reporting Currency].[Local],  Measures.[Month End Rate];

 

Here's the response from Microsoft.

-----

"It looks like the MDX function Divide() will cause the result to lose the format. I would suggest that you try the following 

  1. Define a new calculated member as the following:
  2. CREATE MEMBER CURRENTCUBE.[Measures].[Currency Converted]
    AS Divide([Reporting Currency].[Local],  Measures.[Month End Rate]), 
    FORMAT_STRING = "Currency", 
    VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'Fact Currency Rate'  ;
    2. Modify the above “this=” line to the following:

 

This=[Measures].[Currency Converted];    

Since the calculated member is forced to have the format string, the cell should show the value with the currency format in the scopes.

 

The above is the idea on how we can change the MDX script to force the format. You can use the similar workaround on other measures."

 

----

 

I hope this helps anyone else who runs into the same issue.

 

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