0

I have this table enter image description here

and this is the measurement i have to calculate the average

Traded Contract(MTD) := TOTALMTD(SUM([Traded Contract]), 'TestTable'([Trading Date]))

Average := [Traded Contract(MTD)]/SUM([Trading Days])

Currently the result of average is correct up to daily level, When I wish to see the monthly average, I didn’t filter by date, then I will get the result 9000/14 = 642 which is incorrect, I wish to see 4425 which is the total of each average. How do I amend my Average measurement query to get the expected result

user4815740
  • 311
  • 2
  • 8
  • 17

1 Answers1

1

I'm not entirely sure why you would want to do this since 4425 isn't really an average, but you can write your formula as follows:

Average = SUMX(VALUES(TestTable[Trading Date]),
               [Traded Contract(MTD)] /
               LOOKUPVALUE(TestTable[Trading Days],
                           TestTable[Trading Date],[Trading Date]))

For more information on how these sort of measures work, I suggest reading the following article: Subtotals and Grand Totals That Add Up “Correctly”

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64