Aggregation level control?

[esjewett]

Hi all,

I am having a bit of an issue trying to use Lumira to analyze the CMS (healthcare procedure Medicare charge data) recently released by the US government. The data has the following structure:

Provider ID

Procedure ID

Average payment for this provider & procedure

Number of this procedure billed by this provider

So, in order to figure out the total paid out by Medicare, I would need to create a custom measure taking "Average payment for this provider & procedure" * "Number of this procedure billed by this provider". This works fine if I use a table view and include Provider ID & Procedure ID in the drill-down. But as soon as I remove Provider ID and aggregation kicks in, it goes haywire. We end up with the aggregated "Average payment for this provider & procedure" across all providers multiplied by the aggregated "Number of this procedure billed by this provider" across all providers. Using SUM aggregation on these measures, that clearly results in very very large (and incorrect) number.

I have tried setting the aggregation type of the 2 measures to "None", but that seems to result in the custom measure not being aggregated either.

I would need to get Lumira to calculate the custom measure before aggregation and then aggregate the result. Is that possible?

Thanks,

Ethan

[esjewett]

Update update: I got a Twitter suggestion to try creating a calculated dimension and then create a measure based on that. (Twitter / ccpg04: @esjewett the measures also ...) I *believe* this actually gives the correct result! However, it is extremely slow and results in application errors when I display the tabular view to try to verify the results. In any case, maybe this will be helpful for people with smaller data sets who are looking for this type of functionality in the future.