Forum Discussion

Bonjuga's avatar
Bonjuga
Frequent Visitor
9 years ago
Solved

DAX Calculating Average

Hi Guys,   I have a database that has data looks like following: Transaction ID Status Days 1 received 1 1 sent 2 2 received 3 2 sent 4 2 received from IR 5 3 rec...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Bonjuga,

     

    Please try below measures:

    days for received IR =
CALCULATE (
    SUM ( Sheet2[Days] ),
    FILTER ( Sheet2, Sheet2[Status] = "received from IR" )
)

days for sent =
CALCULATE (
    SUM ( Sheet2[Days] ),
    FILTER (
        FILTER (
            Sheet2,
            CALCULATE (
                COUNT ( Sheet2[Transaction ID] ),
                ALLEXCEPT ( Sheet2, Sheet2[Transaction ID] )
            )
                = 3
        ),
        Sheet2[Status] = "sent"
    )
)

Count transaction =
CALCULATE (
    DISTINCTCOUNT ( Sheet2[Transaction ID] ),
    FILTER ( Sheet2, Sheet2[Status] = "received from IR" )
)

Average = ([days for received IR]-[days for sent])/[Count transaction]

    Best regards,
    Yuliana Gu

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

You should be able to use CALCULATE with a measure to do what you want. So, for example:

 

Measure = CALCULATE(SUM([Days]),FILTER(Table,[Status]="received"))

You could create a similar one for "sent" and then perhaps a third measure to do your calculation?

Bonjuga's avatar
Bonjuga
Frequent Visitor
9 years ago

Thanks for your reply!!  However, I don't want transaction 1 and 4, how can I filter them out so they won't affect my calculation?