Search code examples
powerbidaxdata-analysispowerbi-desktopmeasure

How to calculate average win rate among all the student using DAX in PowerBI

enter image description here

Hi everyone,

I have a sample data as shown in the screenshot above. There are 3 students with different ID : student 123, student 234, student 456. The profit column in the table is how much they earn in each trade.

  • Winning trade = profit > 0

  • Losing trade = profit < 0

Based on the definition above for the winning trade and losing trade, I want to calculate the average winning rate for all the students.

Student 123 - the winning rate is 50% (2 negative profit and 2 positive profit)

Student 234 - the winning rate is 33.3% (2 negative profit and 1 positive profit)

Student 456 - the winning rate is 100% (0 negative profit and 2 positive profit)

So, the final answer, average winning rate among all the students is:

(50% + 33.3% + 100%)/3 = 61.1%

61.1% is the final output that I want, then I will put this value into a Donut chart. I'm relatively new to DAX, any help or advise will be greatly appreciated!

Solution

    1. Please paste text rather than images when providing sample data.

    2. You shouldn't really add averages together like that but if that is definitely what you want, use Measure 2.

    3. If you want a more traditional average to be calculated, use Measure 1.

    enter image description here

    Measure 1 = 
VAR total = CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table','Table'[Student]))
VAR pos = CALCULATE(COUNT('Table'[Profit]), ALLEXCEPT('Table','Table'[Student]),'Table'[Profit] > 0)
RETURN pos/total


Measure 2 = 
VAR students = CALCULATE(DISTINCTCOUNT('Table'[Student]), ALLEXCEPT('Table','Table'[Student]))
RETURN SUMX(VALUES('Table'[Student]), [Measure 1]/students)