Forum Discussion

StainlessSteel's avatar
StainlessSteel
New Member
2 years ago
Solved

Additive Total Issue

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's avatar
    StainlessSteel
    2 years ago

    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]))

1 Reply

  • StainlessSteel's avatar
    StainlessSteel
    New Member
    2 years ago

    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]))