Calculating the average of calculated measures

[pconridge]

Hi

I have a table that contains rows for different student results in different tests. eg

Student identifier, test 1 result, 

Student identifier, test 2 result,

etc

I have created two measures to allow me to display the results in a table:

Measure 1:  this enables me to display a students raw mark.

7A raw Cells = calculate(average('science tracker KS3'[Grade value]),'science tracker KS3'[Resultset]="Year 7" && 'science tracker KS3'[Basic details]="Science Tracker Y7A Cells")

Measure 2: this converts a raw mark into a grade.

7A WL =if('Science measures'[7E raw Mixtures]=0,0.3,

if('Science measures'[7E raw Mixtures] in {1,2,3},0.6,

if('Science measures'[7E raw Mixtures] in {4,5,6},0.9,

if('Science measures'[7E raw Mixtures] in {7,8,9,10,11},0.9,

if('Science measures'[7E raw Mixtures] > 11,1.2)))))

As you can see below I am able to create a table visual that displays these calculated grades for each student and I am able to display an average of the 7A WL measure and 7E WL measure using the DAX:  

AverageScore = ([7A WL] + [7E WL] ) / 2

However as you can see because of absence some students have missed tests and this method does not give me the average of the tests they have sat.  For example the third student in the table is showing an average of 0.45, whereas they have actually only sat one test so the average should be 0.9.  In excel the average function copes with this.  However in DAX I cannot get the average formula to work.

I'd be really grateful for advice on how I might work around this.  Many thanks

Phil

[v-shex-msft]

Hi @pconridge ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[pconridge]

Thank you so much everyone for your support with this.  I have followed the advice of @lbendlin and attach a dropbox link to the full pbix file along with assoicated csv file.  Both have had all sensitive data removed.    You can access the files here:  

https://www.dropbox.com/scl/fo/8h4hd6gknxe9g4dkr8p0z/h?rlkey=s31j0un4dbnnnapqpjq7o4t3s&dl=0

I am unable to get DAX to calculate the average of the WL columns for each student, only including WL scores which are not blank.  I want the outcome to be as follows:  

In Excel I can easily use the "average" formula to only average the non blank values.  However I cant seem to get an averagex or summarize measure to work for me.  Can anyone show me how?  I'd be really grateful for some help.  

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

AverageScore = averagex({[7A WL],[7E WL]},[Value])

Hope this helps.

[lbendlin]

You don't really need DAX for this, this can all be done in the UI.  But if you want, here is an example.

[pconridge]

Thank you so much.  I will study your solution carefully.

[v-shex-msft]

Hi @pconridge,

The measure should calculate based on the row and filter context, perhaps you can try to add a variable with summarize function to aggregate current row context and measure calculation.

Then you can use iterator function to apply the second level aggregate at the first level results.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = average([7A WL],[7E WL])

[lbendlin]

You cannot measure a measure. Instead, implement the complete logic inside a single measure. Read about AVERAGEX etc.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...