Dax aggregation issue on visual total

[sivarajan21]

Hi Team,

I have this below visual whose dax measure doesn't give a correct total:

Dax:

Forecast Budget Unit (TimeSeries) = 
SUMX(
    VALUES(Calendar_[Month-Year]),
    SUMX(
        VALUES(Points[DBName-Point_Id]),
        VAR _target = CALCULATE(
            AVERAGE(TargetTimeSeriesMonth[Dail Units]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _monthtarget = CALCULATE(
            SUM(TargetTimeSeriesMonth[Usage]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _noofdays = COUNTROWS(Calendar_) * COUNTROWS(Points)
        VAR _nofinvdays = COUNTROWS(DataInvoice)
        VAR _missing = _noofdays - _nofinvdays
        RETURN
        IF(
            _missing = _noofdays,
            _monthtarget,
            _target * _missing
        )
    )
)

When I used it in visual, the aggregate shows different value 6,139.11 instead of aggregating to 1,665.07.

PFA file here Portfolio Performance - v2.15 (1).pbix

Please advise!

Thanks in advance!

@marcorusso @Jihwan_Kim @v-linyulu-msft @Ahmedx 

[danextian]

Please see the screenshot below:

Forecast Budget Unit (TimeSeries)2 = 
SUMX ( VALUES ( Calendar_[Month-Year] ), [Forecast Budget Unit (TimeSeries)] )

[sivarajan21]

Hi @danextian ,

Thanks for your quick response!

Can we do it in a single measure instead of two separate measures?

Thanks in advance!

[danextian · Accepted solution]

still two measures.

You  can move this part to an external measure, say _value

VAR _target = CALCULATE(
            AVERAGE(TargetTimeSeriesMonth[Dail Units]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _monthtarget = CALCULATE(
            SUM(TargetTimeSeriesMonth[Usage]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _noofdays = COUNTROWS(Calendar_) * COUNTROWS(Points)
        VAR _nofinvdays = COUNTROWS(DataInvoice)
        VAR _missing = _noofdays - _nofinvdays
        RETURN
        IF(
            _missing = _noofdays,
            _monthtarget,
            _

and then refer to that measure inside your sumx

SUMX (
    VALUES ( Calendar_[Month-Year] ),
    SUMX ( VALUES ( Points[DBName-Point_Id] ), [_VALUE] )
)