Forum Discussion

netanel's avatar
netanel
Post 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 think it's because the formula also calculates BLANK
How do I ignore BLANK in such a formula like this?
  • 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] ) ).

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super 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] ) ).