SSAS calculated member to determine percentage where attribute has value

1
votes

I am new to writing MDX queries and I am having trouble putting a query together for something that should be pretty simple.

The data mart that the cube is based on has a star schema with a central Fact table (centered around items). The fact table has a column for the Date at which the item had been approved. Not all items have been approved, in which case the AprovalDate will be NULL.

What I need to do is display the percentage of items that had been approved when the cube is traversed in a pivot table. I do not quite understand how to define a calculated member that will perform this calculation.

I am using the Measure automatically created by SSAS which counts the number of items ("Item Count"). To possibly make things simpler I have added a named calculation ("Has Status Approved") to the Fact Table with value 1 when an AprovalDate is available and 0 otherwise.

I currently have the following:

CREATE MEMBER CURRENTCUBE.[Measures].[Percentage Approved]
AS IIF(
    [Measures].[Item Count] = 0, 
    0, 
    SUM({[Item].[Has Status Approved].&[1]},[Measures].[Item Count]) / [Measures].[Item Count]
    ), 
FORMAT_STRING = "Percent", 
VISIBLE = 1;

This seems to give me correct results, but all items that are not approved are filtered out of the result (so only rows with 100% Percentage Aproved is displayed).

How should I put the MDX query together to get the desired result? Any help / clarification will be greatly appreciated, I find this all quite confusing

1
ssasmdxolap-cube

1 Answers

1
votes

Do you need the ALL member in the devisor?

CREATE MEMBER CURRENTCUBE.[Measures].[Percentage Approved]
AS IIF(
    [Measures].[Item Count] = 0, 
    0, 
    SUM({[Item].[Has Status Approved].&[1]},[Measures].[Item Count]) / ( [Item].[All], [Measures].[Item Count])
    ), 
FORMAT_STRING = "Percent", 
VISIBLE = 1;