excluding one column in sumx

[Anonymous]

In the below table I want the overall minutes to be always considered as 600 for each row. I wrote the dax for overall minutes as this:

Overall Minutes = SUMX ( SUMMARIZE (Table, Table[date_start], Table[NodeId] ), CALCULATE ( count ( Table[hour_start] )*60 ) )

But it is still grouped by people count.

[Anonymous · Accepted solution]

Thanks. It helped a lot. I changed it to 

Overall Minutes =

CALCULATE (

SUMX (

SUMMARIZE ( table, table[date_start], table[NodeId] ),

CALCULATE ( DISTINCTCOUNT ( table[hour_start] ) * 60 )

),

ALLSELECTED (table[avg_peoplecount])

)

Because I wanted the grouping only ignores the avg_peoplecount but considers a new group per different dates and IDs. It worked correctly in the case of selecting 2 rooms but if I select two dates the overall minutes is 1200 while in the below example, it should be 600:

[Anonymous · Accepted solution]

Thanks. It helped a lot. I changed it to 

Overall Minutes =

CALCULATE (

SUMX (

SUMMARIZE ( table, table[date_start], table[NodeId] ),

CALCULATE ( DISTINCTCOUNT ( table[hour_start] ) * 60 )

),

ALLSELECTED (table[avg_peoplecount])

)

Because I wanted the grouping only ignores the avg_peoplecount but considers a new group per different dates and IDs. It worked correctly in the case of selecting 2 rooms but if I select two dates the overall minutes is 1200 while in the below example, it should be 600:

[Anonymous]

Thanks a lot

[tamerj1 · Accepted solution]

@Anonymous 
Please try

Overall Minutes =
CALCULATE (
    SUMX (
        VALUES ( table[NodeId] ),
        CALCULATE ( DISTINCTCOUNT ( table[hour_start] ) * 60 )
    ),
    ALLSELECTED ( table[avg_peoplecount] )
)

[tamerj1]

Hi @Anonymous 

please try

Overall Minutes =
CALCULATE (
SUMX (
SUMMARIZE ( 'Table', 'Table'[date_start], 'Table'[NodeId] ),
CALCULATE ( COUNT ( 'Table'[hour_start] ) * 60 )
),
ALLSELECTED ()
)