DAX Average Issue

0
votes

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

1
dax

1 Answers

0
votes

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”