DAX sum of rows in column3 only for a subgroup of column2 and only for distinct rows of column1

[SimoLeve]

Hi!

I am loosing hope. Thanks in advance for your help. I have a table with 3 columns

CourseNumber ; TeacherID ; NbOfHoursPerWeek

C1 ; T1 ; 3

C2 ; T1; 2

C2 ; T1 ; 2

C1 ; T3 ; 3

C3 ; T1 ; 5

...

I would need a new column (not an automatic total in a visual), in wich there is the total number of teaching hour (the sum of all the NbOfHoursPerWeek for one teacher) while counting ONLY ONE TIME the hours of a particular course.

For example, above, on the visible rows, we could say that T1 teach a total of 12 hours a week (but I don't care) while he teach different course for only 10 hours (he teach 2 times the course C2).

Here is the expected outcome :

CourseNumber ; TeacherID ; NbOfHoursPerWeek ; NbOfHoursOfPreparation

C1 ; T1 ; 3 ; 10

C2 ; T1; 2 ; 10

C2 ; T1 ; 2 ; 10

C1 ; T3 ; 3 ; 3

C3 ; T1 ; 5 ; 10

Hope I'm clear...

Thanks again in advance!

SL

[EDIT: I made a typo in the table so now it is fixed]

[v-kelly-msft · Accepted solution]

Hi  @SimoLeve ,

First go to query editor and add an index column;

Then create 2 columns as below:

_mark = IF(CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[ TeacherID ]=EARLIER('Table'[ TeacherID ])&&'Table'[CourseNumber ]=EARLIER('Table'[CourseNumber ])&&'Table'[Index]<EARLIER('Table'[Index])))<>BLANK(),1,0)

NbOfHoursOfPreparation = CALCULATE(SUM('Table'[NbOfHoursPerWeek]),FILTER('Table','Table'[ TeacherID ]=EARLIER('Table'[ TeacherID ])&&'Table'[_mark]=0))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[SimoLeve]

Hi! Just had the time to try it and it worked! I was so far, thanks a lot!

[smpa01]

@SimoLeve 

Measure = 
VAR _1 = SUMMARIZE('Table','Table'[teacher#],'Table'[course#],'Table'[hrs])
VAR _2 = CALCULATE(SUMX(_1,[hrs]))
RETURN _2

[SimoLeve]

@smpa01 : Thanks for your help. Sorry for my upcoming newbe questions...

Is the "#" important because I have an error when I try to use it like you did. So I removed it and I got something that look like a duplicate column of hr.

We could use this table to work on the same samble of data :

Table = {("C1","T1",1),("C2","T1",2),("C2","T1",2),("C3","T1",3),("C2","T2",2),("C3","T3",3),("C1","T4",1)}

[Edit:] Ok I understand that you named your column teacher# etc. I named it teacher etc. I though the # was a special key 😂 So it doesnt seems to work on my side 😞

[smpa01]

@SimoLeve Is the "#" important-no

Measure = 
VAR _1 = SUMMARIZE('Table','Table'[teacher],'Table'[course],'Table'[hrs])
VAR _2 = CALCULATE(SUMX(_1,[hrs]))
RETURN _2

[SimoLeve]

Yes, I edited my message about the #... Sorry for my confusion I tought it was a special character, like a joker.

I tried your code and it's doesnt seems to work on my side. I get a duplicate of the hrs column (the duplicate course for one teacher shows in the original table but not in the visual) :

[smpa01]

@SimoLeveplease see how I displayed my measure

in  mine

vs your's

[SimoLeve]

Thanks, I think I understand how to make a total in a visual. But I need a new column for my analysis. After our interactions, I've put more emphasis on what I'm looking for and given an example of the hoped outcome. Thanks a lot for your help, if you have an idea on how to do that, I would appreciate.

[smpa01]

@SimoLeve  as per your data T1=[c1=3, c2=2, c3=5], so theeare all differest courses contrary to what you mentioned - while he teach different course for only 8 hours (he teach 2 times the course C2).

can you also please clearly mention your desired outcome?

[smpa01]

@SimoLeveyou need an index column in your dataset and you can achieve the end goal by creating 4 calculated columns

rank =
RANKX (
    FILTER ( 'Table', EARLIER ( 'Table'[teacher] ) = 'Table'[teacher] ),
    [hrs],
    ,
    ASC,
    SKIP
)

rankCountbyTeacher =
CALCULATE (
    COUNT ( 'Table'[rank] ),
    ALLEXCEPT ( 'Table', 'Table'[teacher], 'Table'[course], 'Table'[rank] )
)

rankingRankCount =
IF (
    [rankCountbyTeacher] > 1,
    RANKX (
        FILTER (
            'Table',
            EARLIER ( 'Table'[teacher] ) = 'Table'[teacher]
                && EARLIER ( 'Table'[course] ) = 'Table'[course]
        ),
        [Index],
        ,
        ASC
    )
)
finalVal =
IF (
    [rankingRankCount] = BLANK (),
    [hrs],
    IF ( [rankingRankCount] = 1, [hrs], 0 )
)

the pbix is attached

https://drive.google.com/file/d/1DMfMDkxG6X0QZMhFRXz8-mc8hGJzKNx1/view?usp=sharing

[SimoLeve]

**bleep**. Sorry, it is fixed now.