Additive Total Issue

[StainlessSteel]

I'm trying to get an additive total, but I am running into the issue below. I've tried using SumX in a few different ways, but I either get the same results or a much larger number. 

The context is that I am creating a matrix that shows the outcome of procedure minutes when compared to a Block Time / Schedule for a team. Some minutes are within the designated blocked time, outside all block time, in another team's block time, or in an 'open' block.

When I try to do totals, I get unexpected results. The rows are correct, but the totals are not.

Here are my current measures:

Inside Block =

VAR IdentifiedTime = FILTER(DIM_MINUTE,DIM_MINUTE[Date_Time]

    in

    SELECTCOLUMNS(

        FILTER(CALENDAR_MINUTE_BLOCKSCHEDULE,CALENDAR_MINUTE_BLOCKSCHEDULE[Program] in SELECTCOLUMNS('NORA Cases',"Program",'NORA Cases'[Derived Program])

        && CALENDAR_MINUTE_BLOCKSCHEDULE[Resource] in SELECTCOLUMNS('NORA Cases',"Resource",'NORA Cases'[RESOURCE])),

    "Date_Time",

    CALENDAR_MINUTE_BLOCKSCHEDULE[Date_Time])

    )

RETURN

    SUMX(

        VALUES('NORA Cases'[Patient Room Minutes]),

    CALCULATE(SUM('NORA Cases'[Minute Count]),IdentifiedTime)

)

Here is the Measure for the 'Wrong Block'

Wrong Block =

VAR IdentifiedTime = FILTER(DIM_MINUTE,DIM_MINUTE[Date_Time]

in

SELECTCOLUMNS(

    FILTER(CALENDAR_MINUTE_BLOCKSCHEDULE,Not(CALENDAR_MINUTE_BLOCKSCHEDULE[Program] in SELECTCOLUMNS('NORA Cases',"Program",'NORA Cases'[Derived Program]))

    && CALENDAR_MINUTE_BLOCKSCHEDULE[Resource] in SELECTCOLUMNS('NORA Cases',"Resource",'NORA Cases'[RESOURCE])

    && CALENDAR_MINUTE_BLOCKSCHEDULE[Program] <> "Open"

    && CALENDAR_MINUTE_BLOCKSCHEDULE[Program] <> "Closed"),

    "Date_Time",

    CALENDAR_MINUTE_BLOCKSCHEDULE[Date_Time])

)

RETURN

SUMX(

    SUMMARIZE('NORA Cases',DIM_RESOURCE[RESOURCE],'NORA Cases'[Derived Program]),

    CALCULATE(SUM('NORA Cases'[Minute Count]),IdentifiedTime)

)

[StainlessSteel · Accepted solution]

I was able to get to what I needed.

Since I have a row for each minute, I made a column with the below expression. I could see if I had any anomolies if it counted more than '1' per row, and then I could just use a Sum in my visuals.

Expression for a new Column, and then Sum of the values in a visual

Inside Block =

CALCULATE(

    COUNTROWS(CALENDAR_MINUTE_BLOCKSCHEDULE),

    FILTER(CALENDAR_MINUTE_BLOCKSCHEDULE,

        CALENDAR_MINUTE_BLOCKSCHEDULE[Resource] = 'NORA Cases'[RESOURCE]  

        && CALENDAR_MINUTE_BLOCKSCHEDULE[Program] = 'NORA Cases'[Derived Program]

        && CALENDAR_MINUTE_BLOCKSCHEDULE[Date_Time] = 'NORA Cases'[Patient Room Minutes]))