SSAS display 1 value based on the time hierarchy and currency

Question

SSAS, I have DAILY, MTD and YTD average balances in three currencies as measures and I want to display only 1 field when I filter by currency or choose month or year level from the hiearchy. thank you in advance

Answer 1

There are different ways to create your cube. For example you can have a measure Amount which is sum of all currencies and doesn't work without a currency selected. In this case you should just do:

SELECT  
    { [Measures].[Amount] } ON COLUMNS,  
    { [Date].currentMember,[Currency].currentMember } ON ROWS  
FROM [ Cube ]  

And it should work. Now of course it all depends on how your cube is created so you might give some more details if you want a better answer. If for example you have 3 measures (one for each currency) then you can either sum them and filter the result like:

 WITH MEMBER MEASURES.[Total Amount] AS
     [Measures].[Amount USD] + [Measures].[Amount EUR] + [Measures].[Amount GBP] 

SELECT  
    { [Measures].[Total Amount] } ON COLUMNS,  
    { [Date].currentMember,[Currency].currentMember } ON ROWS  
FROM [ Cube ]  

The problem with that is that it will still give the total amount if you don't select a currency so you will probably end up with something like:

WITH MEMBER MEASURES.[Selected Currency Amount] AS
    CASE
      WHEN [Currency].currentMember is [Currency].&[EUR] THEN [Measures].[Amount EUR]          
      WHEN [Currency].currentMember is [Currency].&[GBP] THEN [Measures].[Amount GBP]          
      WHEN [Currency].currentMember is [Currency].&[USD] THEN [Measures].[Amount USD]
      ELSE 0
    END

SELECT  
    { MEASURES.[Selected Currency Amount] } ON COLUMNS,  
    { [Date].currentMember,[Currency].currentMember } ON ROWS  
FROM [ Cube ]  

This way for each currency the Selected Currency Amount will be the proper amount. If you don't select currency (remove it from the rows axies) it will be 0