Forum Discussion
Average
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] ) ) ) )
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
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] ) )
)
)
you so much! it worked