Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost 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(
        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 Anonymous Ahmedx 

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

     

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Please see the screenshot below:

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

     

    • sivarajan21's avatar
      sivarajan21
      Icon for Post Prodigy rankPost 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
        Icon for Super User rankSuper 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] )
)