topic Re: Average by more values in DAX Commands and Tips

Average by more values

hanygameel — Sat, 10 Dec 2022 11:34:22 GMT

i have table that have

Patient ID invoice no. Doctor Service No. Amount Episode No. ( visit No)

001 22-01 10 654 50 01

001 22-01 10 859 100 01

002 22-02 09 357 75 01

002 22-02 09 658 650 01

Episode No. serial from 1 for each patient

how can i calculate average amount by Episode for each patient ?

Re: Average by more values

FreemanZ — Sat, 10 Dec 2022 11:52:35 GMT

Hi hanygameel

try to create a measure with this:

AvgByPatientByEpisode =

AVERAGEX(

SUMMARIZE(

TableName,

TableName[Patient ID],

TableName[Episode No.]
),

SUM(TableName[Amount])

)

In case of failure, please provide more data to cover the complexity of your case.

Re: Average by more values

tamerj1 — Sat, 10 Dec 2022 14:30:13 GMT

Hi hanygameel

it depends of which column are you slicing by in your visual. If you are slicing by Patient ID then AVERAGE ( 'Table'[Amount] ) will do. Unless you need to sum the averages at the grand total level the you can use

SIMX ( VALUES ( 'Table'[Patient ID] ), CALCULATE ( AVERAGE ( 'Table'[Amount] ) ) )

Re: Average by more values

hanygameel — Sat, 10 Dec 2022 19:17:26 GMT

Thanks for your quick response

it gives me the total amount

Re: Average by more values

hanygameel — Sat, 10 Dec 2022 19:21:20 GMT

I need to calculate the average episode amount for the doctor

in other way, I need the average amount paid by the patient in the episode ( doctor visit )

Re: Average by more values

hanygameel — Sat, 10 Dec 2022 19:26:37 GMT

Also, i need to exclude zero amount from the average calculation

Re: Average by more values

tamerj1 — Sat, 10 Dec 2022 19:58:14 GMT

hanygameel

Please try

Average Amount =
AVERAGEX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Patient ID],
'Table'[Episode No.],
"@Amount", SUM ( 'Table'[Amount] )
),
[@Amount] > 0
),
[@Amount]
)

Re: Average by more values

hanygameel — Sun, 11 Dec 2022 08:28:40 GMT

i can not understand this

Re: Average by more values

tamerj1 — Sun, 11 Dec 2022 10:34:39 GMT

hanygameel

You can add a column inside SUMMARIZE which is the SUM of the ammount aggregated at the Episode level (which is most probably one value) the FILTER will consider only the values that are above 0 the the averaging is performed.

Re: Average by more values

tamerj1 — Sun, 11 Dec 2022 10:34:42 GMT

hanygameel

You can add a column inside SUMMARIZE which is the SUM of the ammount aggregated at the Episode level (which is most probably one value) the FILTER will consider only the values that are above 0 the the averaging is performed.

Re: Average by more values

hanygameel — Sun, 11 Dec 2022 13:49:32 GMT

Thank you Very Much
i got it