Average

[Anonymous]

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 of the Call ID. For those Costs with an id, I can get an average, but the others not. 

Call ID         COST CAT         Amount

1                 Line Rental             12563

                   Employee Cost       25000

1                 Tax                           9876

2                 Line Rental             23234

2                 Tax                           1111

The average for above would be 35892

Thanks

[v-frfei-msft]

Hi @Anonymous ,

One sample for your reference, here I created a measure as below to work on it.

Measure = 
DIVIDE (
    CALCULATE ( SUM ( Table1[Amount] ), ALLSELECTED ( Table1 ) ),
    CALCULATE (
        DISTINCTCOUNT ( Table1[Call ID] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Call ID] <> BLANK () )
    )
)

[Anonymous]

Thank you both, both formulas worked, but it was not the result I needed.

I made a mistake in description of my problem. I actually need to create a average cost per call id not category. So that the costs that don't have a call id will the be added evenelly over all calls ids. I want to be able to show in a graph month on avg cost per call and then be able to add the Category as a legend and see the average per category per month. 

Some of the categories will not have a call id next to them so the average cost of these categories would be created using the amount of calls in that month. Hope this make sense?

A test PBIX can be downloaded here

https://drive.google.com/file/d/1YZ4I-TmSxsisTsp5X5HKwWr_fDjkVl2p/view?usp=sharing

Thanks for your time

[v-frfei-msft · Accepted solution]

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

[Anonymous]

 you so much! it worked

[d_gosbell]

---

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