Search code examples
powerbicount-unique

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

enter image description here

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:

enter image description here

Solution

  • 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"

    number of students