Forum Discussion

netanel's avatar
netanel
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

AVG Problem

Hey All!   This is my measure: Net USD average per Day = AVERAGEX(     KEEPFILTERS(VALUES('Date'[Date])),     CALCULATE(SUM('Revenues DB'[Net USD])) )   I can not reach the exact amount I ...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    If that is the problem, then you could try this:

    Net USD average per Day =
VAR NetPerDay =
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Net", CALCULATE ( SUM ( 'Revenues DB'[Net USD] ) )
    )
RETURN
    AVERAGEX ( FILTER ( NetPerDay, NOT ( ISBLANK ( [Net] ) ) ), [Net] )

     

    I'm pretty sure AVERAGEX already ignores blank values though, so it might be zero values tripping you up rather than blank values. In this case, you could filter for [Net] > 0 rather than NOT ISBLANK ( [Net] ) ).

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

If that is the problem, then you could try this:

Net USD average per Day =
VAR NetPerDay =
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Net", CALCULATE ( SUM ( 'Revenues DB'[Net USD] ) )
    )
RETURN
    AVERAGEX ( FILTER ( NetPerDay, NOT ( ISBLANK ( [Net] ) ) ), [Net] )

 

I'm pretty sure AVERAGEX already ignores blank values though, so it might be zero values tripping you up rather than blank values. In this case, you could filter for [Net] > 0 rather than NOT ISBLANK ( [Net] ) ).