Hi,
I have a requirement to create a calculated member ‘Sales Invoice as % of Revenue’. I have a ‘document-types’ dimension for which the document-typeIDs 16,19 and 20 indicate a ‘Sales Invoice’.
The catch is that the DimDocumentTypes may not have all of the DocTypeIDs always.
So in case , for eg, the FactSalesRevenue has a revenueAmount of 100 against DocTypeID 16 and 200 against DocTypeID 19 and in case the DimDocumentTypes does not have the member with DocTypeID 20, my calculated member ‘Sales Invoice as % of Revenue’ just shows 0 instead of 300/(total revenue amount.)
What would be the ideal MDX to create the calculated member ‘Sales Invoice as % of Revenue’ ?
What I have tried:
I have been playing around with EXISTING, IIF(VB.ISERROR ..etc