Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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
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!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |