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

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

     

5 Replies

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

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-frfei-msft
        Community Support
        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] ) )
        )
    )