How to get distict values in a duplicated table?

[frost789]

I have a duplicated grades table (linked below)  

https://drive.google.com/file/d/1rxukmKblXTuVgusbKB3cWONutcMADQpO/view?usp=sharing

The table has grades for each course. I need to get Failed percentage, Passed percentage etc. That's accomplished with 

DIVIDE(CALCULATE([GradeCount], 'Sheet1'[P/F/W]= "Fail"), [GradeCount])+0

But the GPA is duplicated for each student. I can't use:

WRONG_Average_GPA = AVERAGE('Sheet1'[OVERALL_LGPA_GPA])+0

Because if a student took more courses, his/her GPA would have weighted more. 

What's the best practice to solve the problem above? My solution is to created 2 tables. One with duplicated records (Grades), and another table with unduplicated records (GPA but each student appears only once in the table). Then use the duplicated table to filter the duplicated table. Is that the way to go? Thank you!

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result very clearly.

[frost789]

With "All" selected in the High School filter,

Fail% should show 25%.

Average_GPA should show 3.26 [This is just the simply average (2.63+3.64+3.51)/3 ].

In my dataset, the GPA is duplicated. So, if a student takes more classes, his/her GPA is weighted more in my measure's calculation. What's the best practice for resolving situations like this?

[v-menakakota]

Hi @frost789   ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @Ashish_Mathur  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the information provided below assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need. 

Thank you.

[v-menakakota]

Hi @frost789 ,

Can you please confirm whether the issue has been resolved. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need. 

Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

GPA = MIN(Sheet1[OVERALL_LGPA_GPA])

WRONG_Average_GPA = AVERAGEX(VALUES(Sheet1[ID]),[GPA])

Hope this helps.

[Ashish_Mathur]

It is the route that i always take.  If my previuos reply helped, please mak that as Answer.

[frost789]

Is this the best practice when there's a duplicated table? Instead of using a relationship?

[Royel]

Hi @frost789 , thank you for asking a question and sharing the sample file. 

You can update your measure 

Failed% = DIVIDE(CALCULATE([GradeCount], 'Sheet1'[P/F/W] = "Fail"), [GradeCount]) + 0

Passed% = DIVIDE(CALCULATE([GradeCount], 'Sheet1'[P/F/W] = "Pass"), [GradeCount]) + 0

Results: 

To get the Correct Average GPA we need to create a calculated table 

Students = 
SUMMARIZE(
    'Sheet1',
    'Sheet1'[ID],
    "StudentGPA", CALCULATE(MAX('Sheet1'[OVERALL_LGPA_GPA])),
    "HS_CODE", CALCULATE(MAX('Sheet1'[HS_CODE])),
    "TERM_CODE_KEY", CALCULATE(MAX('Sheet1'[TERM_CODE_KEY]))
)

Table Relationship: Not Required for now, but you can use it in future 

Results:

Download Power BI File: https://limewire.com/d/193kY#Ip88Uw6YwW

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

[frost789]

Can you answer my question?

[frost789]

Thank you, Royel for your answer. I gathered from your post that SUMMARIZE is the best practice in similar scenarios with duplicated GPA? Also can you explain why you included CALCULATE() for StudentGPA, HS_Code, Term_Code_Key? Is it because of Context Transitioning? 

Students = 
SUMMARIZE(
    'Sheet1',
    'Sheet1'[ID],
    "StudentGPA", CALCULATE(MAX('Sheet1'[OVERALL_LGPA_GPA])),
    "HS_CODE", CALCULATE(MAX('Sheet1'[HS_CODE])),
    "TERM_CODE_KEY", CALCULATE(MAX('Sheet1'[TERM_CODE_KEY]))
)

 The students table does not change if I removed the CALCULATE():

Students = 
SUMMARIZE(
    'Sheet1',
    'Sheet1'[ID],
    "StudentGPA", MAX('Sheet1'[OVERALL_LGPA_GPA]),
    "HS_CODE", MAX('Sheet1'[HS_CODE]),
    "TERM_CODE_KEY", MAX('Sheet1'[TERM_CODE_KEY])
)