How to apply unique count among all the months in PowerBI table visual?

Question

Hi everyone,

I have a table visual as shown in the screenshot above. The Total number of new students shown in the table visual is 210. However, when I manually sum the value, i.e. 20+27+25+31+...+41, i get 216 instead of 210.

I'm using student-id in the table visual and apply Count (Distinct) on it. I guess the potential reason that cause the discrepancy is because of the Count (Distinct) is apply on monthly basis, so it just make sure in that particular month, there is no duplicated student-id. However, when it moved to next month, it will ignore the student-id that appeared in previous month.

For the Total = 210in the table visual, I guess the Count (Distinct) will work across all the months to make sure that the student-id is unique from January to August.

May I know how should I make sure that the student-id only appeared once from January to August and hence show the total value of 210? Any help or advise will be greatly appreciated!

Sample data

student_id    month
100            Jan
101            Jan
102            Jan
100            Feb
103            Mar
101            Mar
104            Feb
100            Jan

Expected output:

please share some dummy data and formulas you've used so far, so we can have an idea about the fact table & check ... — Umut K, Sep 01 '22 at 05:52
check for [ALLEXCEPT](https://learn.microsoft.com/en-us/dax/allexcept-function-dax) — Umut K, Sep 01 '22 at 05:53

score 1 · Accepted Answer · answered Sep 01 '22 at 09:09

try powerquery

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8krMU4rVAfEMUXhGKDyQSrfUJCjPGMjzTSxC0ofgmaCohNsQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student_id = _t, month = _t]),
    #"Removed Duplicates" = Table.Distinct(Source, {"student_id"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"month"}, {{"Number of Students", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

score 0 · Answer 2 · answered Sep 01 '22 at 05:55

0

You should create a measure to count the student-ids and allow your slicers to do the filtering for you.

NewStudents=distinctcount(‘Table1’[Student-ID])

Now drop that measure into your table in place of the Number of New Students column that is currently shown.

answered Sep 01 '22 at 05:55

ZRaptor

101
2
13

It seems like still the same – weizer Sep 01 '22 at 07:19

How to apply unique count among all the months in PowerBI table visual?

Sample data

2 Answers2