How to count attendance of schools from transaction table

Power BI Data Model
1

I have a large transaction table with a list of customers, which are schools.

Every customer has a certain enrollment, I want to create a measure which counts up the enrollment of every customer in my list, without double counting a customer which appears more than once in the list. Every time a particular customer appears, the enrollment column displays it’s student enrollment total.

I can’t simply take the sum of the enrollment column because it will double count customers that appear more than once.

Enrollment Scenario.xlsx (9.9 KB)

I don’t want to create a new table. I realize I can copy the table in power query and then remove duplicates, then count the enrollment column which now contains a unique list of customers. However I’m looking for a way to do this entirely in DAX without create another physical table.

2

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition that you’ve specified. Write a small measure as provided below -

Total Enrolment = 
SUMX(
    DISTINCT( Data[Customer] ) , 
        FIRSTNONBLANK( Data[Enrollment] , 0 ) )

Below is the screenshot of the final results provided for the reference -

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Enrollment Calculation - Harsh.pbix (17.9 KB)

1 Like