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:
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?