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.

[esjewett]

Just tried this in Tableau Public and (while it can't handle the full 9-million-row data set), it looks like it runs the measure calculation at the lowest level of detail before aggregating. That seems like the safe, if not the efficient choice, and results in the correct totals.

I'm assuming at the moment that there is no control over this in Lumira, so I guess the solution would be to update my data set with the actual measure value in a new column. Or am I missing something?

[andreasja_schneider]

How about using Webi Rich client instead ? : ))...

And as usual.. have you maybe posted this as a new feature request in ideas.sap.com for Lumira?