Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Donna
Frequent Visitor

Summing up one column by distinct ID in another column

I am a Power BI newbie and trying to replicate something I can easily do in Excel. Students can have multiple ethnicities. EFTS (Equivalent Full Time Student) is a measure of their enrolment. The following student enrolled in 0.2304 EFTS. If split by ethnicity they have 0.2304 for each ethnicity but, as a whole student still only a total of 0.2304 EFTS.

My issue is that Power BI is that I can't get a value for the whole student - Power BI is summing up both ethnicities which duplicates the total. This is the formulae I am using but obviously it's not right.

Sum Distinct EFTS =
          SUMX(DISTINCT(SDR[Student ID]), SUM(SDR[All EFTS]))
Sum Distinct Successful EFTS = (
    SUMX(DISTINCT(SDR[Student ID]), [Successful EFTS]))
 
I've worked out how to count the rows but I can't figure out how to apply this when summing:
Countrows = COUNTROWS(DISTINCT(SDR[Student ID]))
 
Any help would be very appreciated.
 

Excel.JPGPower BI.JPG

 

 

1 ACCEPTED SOLUTION
Donna
Frequent Visitor

I have now realised that my data import was flawed. Where students had 2 or 3 ethnicities, I was importing two (or three) different sets of data in a flat file so I was never going to get a distinct count unless I did some extremely fancy formulas saying "don't include ethnicities 2 or 3 in the total". Way, way beyond my abilities.

I have now removed ethnicity from my data import and created a separate table with Student ID and ethnicity and created a relationship between them. Problem solved.

Thanks everyone who looked at this.

View solution in original post

3 REPLIES 3
MartynRamsden
Solution Sage
Solution Sage

Hi @Donna 

 

Try this:

SUM Distinct EFTS =
SUMX(
    DISTINCT( SDR[Student ID] ),
    DIVIDE(
        CALCULATE(
            SUM( SDR[All EFTS] ),
            SDR[Student ID] = EARLIER( SDR[Student ID] )
        ),
        CALCULATE( 
            COUNTROWS( SDR ), 
            SDR[Student ID] = EARLIER( SDR[Student ID] ) 
        )
    )
)

 

You can use the same pattern for your other measure, just replace the column name in the first CALCULATE expression.

 

Best regards,
Martyn

 

Hi @MartynRamsden 

 

Thank you for your reply. I'm not quite there yet (but the total matches the rows which is progress)

I think that the student's course enrolments might be contributing to the problem. I followed through your DAX logic in Excel and, when I bought courses in I can then see how Power BI has arrived at 0.0768.

I'm not sure how to fix this though - I need it to be able to aggregate the EFTS at course, ethnicity and student levels. 

Thanks for your help!

 

Power BI v2.JPGExcel v2.JPG

Donna
Frequent Visitor

I have now realised that my data import was flawed. Where students had 2 or 3 ethnicities, I was importing two (or three) different sets of data in a flat file so I was never going to get a distinct count unless I did some extremely fancy formulas saying "don't include ethnicities 2 or 3 in the total". Way, way beyond my abilities.

I have now removed ethnicity from my data import and created a separate table with Student ID and ethnicity and created a relationship between them. Problem solved.

Thanks everyone who looked at this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.