Percentage of Total using DISTINCTCOUNT

[malw]

I've got a Measure which counts the number of distinct Students in one of our dimensional tables:

No. of Students:= DISTINCTCOUNT('Student'[Student Id])

What I want to show is that same measure as a Percentage of the total number of students.

Currently I tried the following:

Total Student Count:=CALCULATE( [No. of Students] , ALL(Student) )

% of Students:=DISTINCTCOUNT('Student'[Student Id])/[Total Student Count]

This works correctly when I'm only wanting to slice/dice by attributes of Student, but as soon as I try to slice/dice by any other dimensional attribute "% of Students" always shows as 100% for every row.

Appreciate I'm quite new to DAX so I'm sure there's something simple I'm overlooking. Any help is much appreciated!

[MattAllington]

Welcome to DAX and Power Pivot.  There is a lot of foundational material you need to learn to be able to understand how DAX works.  If you turn on bi-directional filtering between your dimension tables and any common fact tables this "problem" should go away.

I have written a long blog post that goes live tomorrow morning (Sydney Time) that talks in detail about filter propagation (among other things).  This link below wont work until it goes live, but you can take a look in about 14 hours from now and have a read then.

http://exceleratorbi.com.au/many-many-relationships-dax-explained/ 

You may also like to take a look at my book http://xbi.com.au/learndax  I am sure that will help you get to where you want to be.

[malw]

Thanks Matt. We've got bidirectional filtering turned on already for all tables. That in itself doesn't appear to be the problem - the bidirectional filtering is working for all other instances.

[GilesWalker]

@malw - Are you using multple tables? If so are the relationships set up correctly? If not then this can cause your percentage to jump to 100%.

The only other thing I think you could look at if the above doesnt work is to use the formula DIVIDE:

% of Students:= DIVIDE([Total Student Count],[No. of Students])

The Divide function removes any answers with 0's and only calculates cells with data.

Hope this helps,

Giles

[malw]

Thanks Giles, appreciate the reply.

@malw - Are you using multple tables? If so are the relationships set up correctly? If not then this can cause your percentage to jump to 100%.

We are using multiple tables, the relationships are all bi-directional and are keyed using typical star-schema surrogate keys. All of our other measures are slicing and dicing correctly via all dimensional attributes so the relationships are appearing to work as intended.

The only other thing I think you could look at if the above doesnt work is to use the formula DIVIDE:

% of Students:= DIVIDE([Total Student Count],[No. of Students])

Thanks, I'll give this a try and report back.

[Sean]

This is not what's causing your problem but % Of Students = DIVIDE ([No. of Students], [Total Student Count])

Should be Number/Total NOT Total/Number

Matt is right your problem occurs because the filter does not propagate to the student table.

Can you post of a screenshot of the Relationship View so we can see the relationship between the Student table and the table you are using to filter which does not work?