Additive Total Issue

StainlessSteel — Sun, 03 Dec 2023 14:12:02 GMT

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]

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]

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)

)

Re: Additive Total Issue

StainlessSteel — Sun, 03 Dec 2023 19:09:32 GMT

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

topic Additive Total Issue in DAX Commands and Tips

Additive Total Issue

Re: Additive Total Issue