How to define a sum with a groupby

[Fran1991cesco]

Hi all,

I am having a problem with a table just like to one that I show you here:

At first I created a new column with the following formula:

SUM = 'Table'[Hours Course 1] + 'Table'[Hours Course 2] + 'Table'[Hours Course 3]

Then, I need to count the number of students that have done in total less then 15 hours, such as Student 4 an Student 3. For this I am using this formula:

NumberOfStudent = CALCULATE( DISTINCTCOUNT('Table'[Name]), FILTER('Table', SUM('Table'[SUM]) < 15.00) ) 

 In the graphs that I have, the formula does not return the right number. Even if I remove the sum for the 'SUM' column, the result is off. Can someone help me please?

[v-xinruzhu-msft]

Hi @Fran1991cesco 

You can refer to the following link.

You can modified your sum column:

SUM = SUMX(FILTER('Table',[Name]=EARLIER('Table'[Name])),'Table'[Hours Course 1] + 'Table'[Hours Course 2] + 'Table'[Hours Course 3])

Then 

1.You can create a new column:

NumberOfStudent = CALCULATE( DISTINCTCOUNT('Table'[Name]), FILTER('Table', [SUM]< 15.00) ) 

2.You can create a new measure

NumberOfStudents = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER(ALLSELECTED('Table'),[SUM]<15))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ITManuel · Accepted solution]

Hi @Fran1991cesco ,

create I measure and try the following code.

COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Name] ),
            "SUM", CALCULATE ( SUM ( 'Table'[SUM] ) )
        ),
        [SUM] < 15
    )
)

This will give you the number of students with in total less than 15 hours. 

Br