Is there a way to show a measure only once when we have repeating dimension values?
We have a scenario where an insurance policy has multiple drivers. However, there is only one insurance premium at the policy level. If we add drivers to a discovery with a measure it replicates the measure and then totals to a wrong number. Here is sample data:
Policy Table
Drivers Table
These two tables join by the Policy ID field.
When I create a discovery I get the following. Is there a trick to have the $12,001 show once or have the total reflect the policy level value of 12,001 instead of summing it five times, getting the wrong value of $60,005.00?
Thanks,
Rob Clark
4 replies
-
Hi Robert Clark,
Instead of using the grand total, you can use the subtotal along with the average. This approach will help in analyzing multiple policy details.
Hope its help you.
Thanks,
Vijayan Krishnan
-
Thank you Vijayan. I had thought of this as well and it works as along as we don't have a grand total. When I add a grand total it is way off and doing an average for grand total is wrong as well. Any thoughts on a grand total? One other way we have been able to kind-of make it work is to use a formula and assign the premium amount to the first driver on the policy, but this is not ideal.
-
Hi
Make sure your subtotal settings are set to AGGREGATE and not SUM and it should give you the right answer.
Hope that helps.
Ian