Forum Discussion

sivarajan21's avatar
sivarajan21
Post Prodigy
1 year ago
Solved

Dax aggregation issue on visual total

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( VAL...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    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] )
)

     

sivarajan21's avatar
sivarajan21
Post Prodigy
1 year ago

Hi danextian ,

 

Thanks for your quick response!

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

 

Thanks in advance!

danextian's avatar
danextian
Super User
1 year ago

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] )
)