Average by more values

[hanygameel]

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 ?

[FreemanZ · Accepted solution]

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. 

[hanygameel]

Thanks for your quick response

it gives me the total amount

[tamerj1]

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

[hanygameel]

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 )

[tamerj1]

@hanygameel 

Please try

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

[hanygameel]

i can not understand this

[tamerj1]

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

[tamerj1 · Accepted solution]

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

[hanygameel]

Thank you Very Much
i got it

[FreemanZ · Accepted solution]

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. 

[hanygameel]

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