Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Average

Hi all   I have a table with costs. Some of the Costs have a Call ID and others not. There is also a Cost Category. I'm trying to get the average cost per cost category based on the Distinctcount o...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    To use the measure as below.

     

    Measure =
VAR countcall =
    CALCULATE (
        COUNT ( tets[Call ID] ),
        FILTER ( tets, tets[Call ID] <> BLANK () )
    )
RETURN
    IF (
        ISFILTERED ( tets[INVOICE_ITEM_CATEGORY] ) = FALSE (),
        BLANK (),
        IF (
            ISBLANK ( countcall ),
            AVERAGE ( tets[GBP_AMOUNT] ),
            DIVIDE ( SUM ( tets[GBP_AMOUNT] ), DISTINCTCOUNT ( tets[Call ID] ) )
        )
    )

     

d_gosbell's avatar
d_gosbell
Super User
7 years ago
Anonymous wrote:

 For those Costs with an id, I can get an average, but the others not. 

 

Yes, that is the behaviour I would expect to see. If you did the average of Employee Cost for example you would do 25000 / Blank() which would return Blank(). 

 

If you wanted to just show the value of the [Amount] if the Call ID is blank you could maybe do something like the following:

IF ( DISTINCTCOUNT( table1[Call ID] ) < 1, [Amount], DIVIDE( [Amount], DISTINCTCOUNT( table1[Call ID] ) )