Sum by a column, compare with threshold and count distinct

[ismail95]

I try to create below measure, and need your help.

The table is:

I want to create a measure which Sum Knowledge column Grouping by Task Column. After that compare if the sum of knowledge level is bigger than Team Knowlegde Threshold.

Them Knowledge Thresold is

1 for Team 1 and

2 for Team 2.

After comparing, Distinct count how many tasks are below the thresold.

So sum of Knowledge per Task for this example are

Task A = 2

Task B = 1

Task C = 3

Task D = 1

Sum of 2 tasks are below the thresold.

Sum of Knowledge for Task B <= 1

Sum of Knowledge for Task D <= 2

so the output of measure will be 2.

This is my code, it works but the threshold is a constant and 2 . I couldnt manage to provide conditons above.

UnderThethreshold =
CALCULATE(
DISTINCTCOUNT('Table'[Task]),
FILTER(
all('Table'[Task Name]),
CALCULATE(SUM('Table'[Knowledge]))<= 2
)
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

expected result measure: =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    'Table',
                    "@tasksum",
                        CALCULATE (
                            SUM ( 'Table'[Knowledge] ),
                            FILTER ( 'Table', 'Table'[Task] = EARLIER ( 'Table'[Task] ) )
                        )
                ),
                "@condition",
                    SWITCH ( TRUE (), 'Table'[Team] = "Team 1", 1, 'Table'[Team] = "Team 2", 2 )
            ),
            'Table'[Task],
            [@tasksum],
            [@condition]
        ),
        [@tasksum] <= [@condition]
    )
)