Matrix Incorrect Total

[ivanuska]

Hi guys,

I am having issue with total row in matrix for one of my measure that is calculating 3-month average. If the measure is evaluated on date level, it is showing correct figures - see below graph and also table (on date level). Problem is only in total.

I tried to implement HASONEVALUE condition however it did not resolve the issue.

Measure = 
VAR _average = CALCULATE([3-Month Average Invoice Amount USD])
RETURN
IF(HASONEVALUE('Date table'[Date]),
    _average,
        SUMX(VALUES('Date table'[Date]) , _average ))

The sum of the first 3 day-averages should sum up to 7.1 mil. (as in graph) and not 5.4 mil. (as in matrix)

Any help is much appreciated!

Thank you
Ivanuska

[edhans · Accepted solution]

Try this:

New Measure =
SUMX (
    VALUES ( 'Date table'[Date] ),
    [3-Month Average Invoice Amount USD]
)

That will at the total level take the sum of the 3 averages of each day.