Get median of rows with same values

[Idigorasu]

Hi,

I have a table with data like this:

What I need is to get the median of each row that has the same Question and Teacher.

So, Question 1 for John would be: 8 + 5 = 13/2 = 6,5

Is this possible?

[v-shex-msft]

Hi @Idigorasu.,

You can use the following measure formula to get average aggregate based on filtered table records and current row contents 'Question','Teacher' categories labels.

Measure =
CALCULATE (
    AVERAGE ( Table[Score] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Question] ),
    VALUES ( Table[Teacher] )
)

Regards,

Xiaoxin Sheng

[JarroVGIT · Accepted solution]

Hi @Idigorasu ,

Please remember never post pictures of tables, it is completely useless to the people who are helping you and now we have to type over your date (which could have been avoided and sped up the process of helping you :)).

It isn't clear if you want a calculated column or a measure so I assume a calculated column for now. 

The second thing is the word 'median' which means something else than 'average'. The median of a set of numbers is that number where half the numbers are lower and half the numbers are higher. The average of a set of numbers is the total of those numbers divided by the number of items in that set. The median and the average might be close, but they could also significantly different. It all depends on the numbers. 

In your case, you are looking at the average ( ( 8 + 5 ) / 2) = 13. So I am going to assume you meant average and not median. 

The calculated column dax is this:

AverageTeacherQuestion = 
VAR _curTeacher = Table2[Teacher]
VAR _curQuestion = Table2[Question]
RETURN
AVERAGEX(FILTER(Table2, Table2[Question] = _curQuestion && Table2[Teacher] = _curTeacher), [Score])

Resulting in this:

You can find my PBIX here, it is Table2 and you can ignore other tables (these are for other questions). Let me know if this works for you!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Idigorasu]

Hi @JarroVGIT ,

1. I apologize, I didn't think about the picture. Lesson learnt!
2. Yes, you are right. I am looking for the average, not the median.
3. That is formula I was looking for. Thank you so much! 

I just started with PowerBI and this community has helped me so much, thank you for your work.

Have a nice day.

[JarroVGIT]

Great to hear and have fun with your learning journey! It really is just a very nice tool 🙂 if you have any other questions, just tag me in your message 🙂
Kind regards,
Dkerro123