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

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       you so much! it worked