Should be easy, but still learning DAX

[afaherty]

Hi All,

I think this should be easy, but I'm still learning the ins & outs of DAX and I'm struggling.

I have this unpivoted data, and need to create a bar graph the % of unique students in each grade in each city (grade will be on the legend, city on the slicer).  I already have a matrix of the total number of unique students which I achieved easily with DISTINCTCOUNT of IDs, but I'm struggling with the % of unique students by city:

Student ID	Grade	City
1	Sophomore	Albany
1	Sophomore	Albany
1	Sophomore	Albany
2	Sophomore	Tallahassee
3	Sophomore	Pittsburgh
4	Sophomore	Richmond
5	Sophomore	Richmond
6	Junior	Pittsburgh
7	Junior	Tallahassee
7	Junior	Tallahassee
8	Junior	Richmond
9	Junior	Richmond
10	Junior	Albany
10	Junior	Albany
11	Junior	Pittsburgh
12	Junior	Tallahassee
12	Junior	Tallahassee
12	Junior	Tallahassee
13	Junior	Albany
13	Junior	Albany
14	Junior	Pittsburgh
15	Junior	Richmond
16	Senior	Tallahassee
17	Senior	Pittsburgh
17	Senior	Pittsburgh
18	Senior	Albany
18	Senior	Albany
19	Senior	Richmond
20	Senior	Tallahassee
21	Senior	Richmond
22	Senior	Albany
22	Senior	Albany
23	Senior	Richmond

Thank you to anyone who can help.

[smpa01]

@afaherty  can you please create new thread and post the representative pbix please?

[smpa01]

@afaherty  try this

Measure = CALCULATE(DISTINCTCOUNT('Table'[Student ID]),ALLEXCEPT('Table','Table'[Grade],'Table'[City]))

[afaherty]

@smpa01 Hi! Thank you, you always come to my rescue!  I had already gotten the totals, but now I need to figure out the percentages which is where I'm stuck! 😓

[smpa01]

@afaherty  please provide the desired output for the given data

[afaherty]

<Redacted>

[smpa01]

@afaherty  please walk me through  how are you getting the following?

[afaherty]

@smpa01 

Apologies, had a small miscalc.

Here is what it would be for the Juniors:

Sophomores:

Seniors:

[smpa01 · Accepted solution]

@afaherty 

Measure = 
VAR _num = CALCULATE(DISTINCTCOUNT('Table'[Student ID]),ALLEXCEPT('Table','Table'[Grade],'Table'[City]))
VAR _denom = CALCULATE(DISTINCTCOUNT('Table'[Student ID]),ALLEXCEPT('Table','Table'[Grade]))
RETURN DIVIDE(_num,_denom)

[afaherty]

Thank you @smpa01!  Worked like a charm.  I'm having a separate issue though where my slicer (which is "City") isn't working for the new matrix created with your measure.  It's showing me all of the cities instead of whichever one I choose.  All of the other visuals on the report canvas are complying with the slicer.  Any ideas?