Sumproduct / Weighted Average in DAX

DAX DAX Calculations
1

Hi, I don’t know how to address this question, but will try to explain. I’m trying to calculate the weighted average of a student as you see in the screen shot. The percentage of each student is multiplied by the number above and then totaled to get the weighted average. I have the PBIX file and the excel file attached. I have done it in Excel and you can find the weighted average in Column J for Year 6, but cannot get a workaround using DAX.
Kindly advice the solution.

Student Name 1 2 3 4 5 6 W.Avg
AAHIL ARSHAD SALAM 0% 0% 60% 20% 0 0% 2.6
AAMNA SIDDIQUI 0% 0% 0% 0% 1 0% 5
MARINELLE REBECCA SERRAO 0% 0% 0% 50% 50% 0% 4.5

Spring 1 complete.xlsx (551.1 KB)
School Reports Copy.pbix (145.7 KB)

2

Hello @chris786,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition that you’ve specified. Below are the measures alongwith the screenshot of the final results provided for the reference -

1). Weighted Average =

W. Avg = 
[Grade %] * SELECTEDVALUE( Sheet1[Marks])

2). Weighted Average - Grand Totals =

W. Avg - Totals = 
VAR _Cross_Joins =
CROSSJOIN(
    VALUES( Sheet1[Student Name] ) , 
    VALUES( Sheet1[Marks] ) )

VAR _Virtual_Table = 
ADDCOLUMNS(
    _Cross_Joins , 
    "@Totals" , 
    [W. Avg] )

VAR _Remove_Blanks =
FILTER( _Virtual_Table , 
    NOT ISBLANK( [@Totals] ) )

VAR _Results =
SUMX(
    _Remove_Blanks , 
        [@Totals] )

RETURN
_Results

Final Results
Final Results1889×1089 238 KB

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

School Reports Copy - Harsh.pbix (145.6 KB)

1 Like
3

Thanks Harsh, Wonderful solution, I need to think Tables…CROSSJOIN…was key here I guess…
Learnt a pro tip here… :smiley:

4

Hi Harsh, quick question…
I noticed that you calculated the measure of W. Avg first as a separate measure and not inside the Weighted Average - Grand Totals measure, is there a particular reason for this.
Although W. Avg is being referenced within the main measure (Weighted Average - Grand Totals), I wanted to understand if it had anything to do with the SELECTEDVALUE and if it could be worked inside the main measure itself. Thanks once again.