Sum by a column, compare with threshold and count distinct

ismail95 — Fri, 12 Apr 2024 06:57:13 GMT

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

Re: Sum by a column, compare with threshold and count distinct

Jihwan_Kim — Sun, 14 Apr 2024 07:05:31 GMT

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

topic Re: Sum by a column, compare with threshold and count distinct in DAX Commands and Tips

Sum by a column, compare with threshold and count distinct

Re: Sum by a column, compare with threshold and count distinct