Forum Discussion
Average by more values
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 ?
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.
- 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.
10 Replies
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.
Also, i need to exclude zero amount from the average calculation
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] ) ) )
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 )
Please try
Average Amount =
AVERAGEX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Patient ID],
'Table'[Episode No.],
"@Amount", SUM ( 'Table'[Amount] )
),
[@Amount] > 0
),
[@Amount]
)
Thanks for your quick response
it gives me the total amount
