Calculated table grouped by a field (done) and labeled with conditional lookup using MAX function

[cconnolly]

Hi Everyone,

I work for a hospital and am working on a report to show total charges for each patient labeled with the CPT code (billable code for outpatient hospital services) that has the highest charge amounts.  This is considered the 'main CPT code' on the account. I can't include the real data for obvious reasons, but the data is formatted similar to the example below:

Note that goal output table includes all of the charges summed into one line, which I can handle by creating a calculated table.  The issue I'm having is how to lookup in the CPT code with the largest charge amount.  The data I'm using will be about ~2M rows by EOY and is super wide so I want to do this as efficiently as I can.  Any advice?

Hope that this is enough info for you to understand my issue! Happy to add any color that would be helpful.

Thanks,

Charlie

[v-yalanwu-msft · Accepted solution]

Hi, @cconnolly ;

I changed the simple.

Then you could create a new calculate table.

Table = 
SUMMARIZE(Data,Data[Patient],
"CPT",CALCULATE(MAX('Data'[CPT]),FILTER('Data',[Charges]=CALCULATE(MAX('Data'[Charges]),ALLEXCEPT(Data,'Data'[Patient])))) ,
"Charges",sum(Data[Charges]))

the final show:

Or you could create a measure.

maxcpt = 
CALCULATE(MAX('Data'[CPT]),FILTER(ALL(Data),[Patient]=MAX('Data'[Patient])
&& [Charges]= CALCULATE(MAX('Data'[Charges]),ALLEXCEPT(Data,'Data'[Patient]))))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cconnolly]

The summarized table was exactly what I needed. Thank you!

[ryan_mayu]

@cconnolly 

you can just add the column to table visual

or create a table

Table = SUMMARIZE(Data,Data[Patient],"CPT",max(Data[CPT]),"Charges",sum(Data[Charges]))

pls see the attachment below

[cconnolly]

Thank you! @ryan_mayu 

This is slightly different that what I'm trying to do.  My example was ambiguous so that's my fault.

Instead of including the highest CPT code by the CPT number, I want to include the CPT code with the largest amount in the charge column.  Your query functions correctly because in my example the highest CPT codes have the largest charges but it doesn't work in my actual data set.

Thank you,

[ryan_mayu]

@cconnolly 

could you pls provide the sample data which is close to your acutal data?