Total SUM fails - all is fine on each row

[petluo]

Hi,
I've spent way too much time on this now, so I have to ask for some help.

I have 2 tables:
data: holds data from git commits
Dates: holds a full range of dates
The tables are related via Dates[date] ==> data[commiterDate]

This code works for counting the number of work days in selected date span on each row.
(The user can set a date span via a date slider control).

Sum wrk.days = 
COUNTROWS(
    FILTER(
        DATESBETWEEN(Dates[Date], MIN(data[committerDate]), MAX(data[committerDate])),
        WEEKDAY(Dates[Date], 2) < 6
    )
)

My problem is that the total summary at the bottom of the table fails.
I think I have to use SUMX in some way, but I can't figure out how.

Sum should be 7...

I hope someone can help me.

Best regards

[AlexisOlson · Accepted solution]

If good.spread and oneCommitter overlapped a day, then the total makes sense.

If you want to sum the rows, then you need to iterate over commiterid somehow. One option:

Commiter Work Days = SUMX ( VALUES ( data[committerId] ), [Sum wrk. days] )

[petluo]

Thank you, it works!

Just the kind of simple solution I was looking for. 👍

The code now:

Wrk.days = 
SUMX( 
    VALUES(data[committerId]), 
    [Wrk.days row count] 
)

---

Wrk.days row count = 
VAR cr = 
COUNTROWS(
    FILTER(
        DATESBETWEEN(Dates[Date], [MinDate], [MaxDate]),
        WEEKDAY(Dates[Date], 2) < 6
    )
)
RETURN 
IF(
    cr > 0,
    cr,
    1
)

[smpa01]

@petluo  can you try this

Measure =
SUMX (
    SUMMARIZE (
        ADDCOLUMNS (
            data,
            "daysCount",
                CALCULATE (
                    COUNT ( Dates[Date] ),
                    (
                        FILTER (
                            DATESBETWEEN (
                                Dates[Date],
                                CALCULATE ( MIN ( data[committerDate] ), ALLEXCEPT ( data, data[committerid] ) ),
                                CALCULATE ( MAX ( data[committerDate] ), ALLEXCEPT ( data, data[committerid] ) )
                            ),
                            WEEKDAY ( Dates[Date], 2 ) < 6
                        )
                    )
                )
        ),
        data[committerid],
        [daysCount]
    ),
    [daysCount]
)

[petluo]

Thank you!