Matrix and distinct counts total issue

[SO]

I am stuck trying to find a total using eithe a Matrix or Table to calculate the number of distinct courses for staff.  I have a number of slicers that is slicing down the data.  I'm also using an Analysis Server so I don't have access to the raw data.  

Here is a simplified version of what the raw data behind tables should look like.  I know that I have Staff (1-5), Depts(A-I), and Sections (A12 - I78).  I'm tryin to count the number of distinct Sections per Person, or how many distict sections each Staff has.      

Staff    A        B         C         D          E          F          G         H        I            Total

1         A12               C35      D12      E22                                                       4 

2                              C55                              F23                                            2

3         A44    B34                                                                 H12    I23          4

4                   B12                                                     G23                               2

5         A12    B22     C72      D44      E55                                       I78           6

But when I set the count to Distinct Count, I can get each course to count correctly but not the Column or Row Totals

 Staff    A        B        C        D          E          F         G         H           I         Total

1           1                   1         1           1                                                         9 

2                                1                                1                                               9

3          1         1                                                         1                   1            9

4                     1                                                                 1                         9

5          1         1         1         1           1                                            1           9

Total    1         1         1         1           1          1          1          1        1           9   

I tried to develop a Measure with out success. 

Count of Deptment running total in Staff =

CALCULATE(

    DISTINCTCOUNT('Course'[Dept]),

    FILTER(

        ALLSELECTED('Team'[Staff]),

        ISONORAFTER('Team'[Staff], MAX('Team'[Staff]), DESC)

    )

)

If I use count, then the it appears that the Matrix is turning the text of the course into a number.  

Ultimatley, I would like to see something like 

Staff     A        B        C        D          E          F         G          H          I         Total

1          1                   1         1          1                                                         4 

2                               1                                1                                              2

3          1         1                                                         1                   1          4

4                     1                                                                  1                      2

5          1         1         1         1          1                                             1         6

Total    3         3         3         2          2          1          1        1           2        18   

OR

Staff   Total

1         4 

2         2

3         4

4         2

5         6

Total   18

Any thoughts would be appreciated?  

[v-lid-msft · Accepted solution]

Hi @SO ,

We can try to use the following measure to meet your requirement:

Measure =
SUMX (
    DISTINCT ( 'Team'[Staff] ),
    CALCULATE (
        SUMX (
            DISTINCT ( 'Course'[Dept] ),
            CALCULATE (
                DISTINCTCOUNT ( 'Course'[Sections] )
            )
        )
    )
)

Best regards,

[SO]

Thanks for your help with this, measure.  With a few tweaks to match my actual fields I was able to make this work.  So many thanks. 

[v-lid-msft]

Hi @SO ,

What is the relationship between those two tables, is "one to many" or "many to many"? What is the column the relationship based on? Could you please share two raw tables based on fake data and your expected table you have shared, if it does not contain any confidential information?

Best regards,

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907