Search code examples
aggregate-functionsssasmdxdimensionscube

Subtract 2 measures as Grand Total in SSAS Cube Browser

I guess it's a common and basic issue. I'm trying to get the "net value" of a measure depending on a dimension. Let me explain..

I have a fact table factTicket and a dimension called Operation who have 2 values: Opened and Closed. The goal is to get the number of ticket opened and closed for each month. In the Cube Browser, I have a Distinct Count Measure of the TicketID according to the Operation dimension, but I would like the Grand Total to be Opened minus Closed instead of Opened + Closed

The factTable has a record with the date for every opened ticket, and a second record is added when the ticket is closed (So a Ticket can only have a maximum of 2 records in the fact table)

Is it possible ? I can't find a way to do it.. Maybe the Operation Dimension is a bad idea and I should have 1 record per ticket, with an OpenedDate and ClosedDate field ? I don't believe it will fixe the probleme since Distinct Count always return positive value, so the Grand Total will still be Opened + Closed.

Any ideas is welcome ! Thx

Solution

  • The scope function is what I needed:

    SCOPE([Dim Operation].[OpenClose].[All], [Measures].[TicketCount]);
THIS = [Dim Operation].[OpenClose].&[OPEN] - [Dim Operation].[OpenClose].&[CLOSE]
END SCOPE;