Hi,
I'm trying to create a measure which is a division between two different metrics, that can be chosen from a set of possible KPIs by the user. Some of these measures are available in a less countries than others.
Let's say that the data looks something like this:
| Measure | Countries | Internal/external |
|---|
| Measure_1 | Germany, UK, Denmark, Sweden, US | is_Internal = 1 |
| Measure_2 | UK | is_Internal = 0 |
| Measure_3 | UK | is_Internal = 0 |
| Measure_4 | Germany, UK, Denmark, Sweden, US | is_Internal = 1 |
The data model has all countries for each measure and in case no value is available for that country, the value is set to 0.
Note: I have tried excluding the missing countries for some measures but this solution does not work.
If the division is between Measure_2 and Measure_3 or Measure_1 and Measure_4, there is no problem since the data is available in the same countries, but if I try to divide Measure_2 by Measure_4, the metric stops making sense.
At the moment I have fixed this issue with and IF statement in the measure calculation, but I don't like this solution since it seems very inefficient:
IF (MIN({<[Metric]={'$(=Denominator)'}>} is_Internal) = 0
or MIN({<[Metric]={'$(=Numerator)'}>} is_Internal) = 0 ,
sum({$<[Measure]={'$(=Numerator)'},Country = P(external_country)>} [Measure_Value])
/
sum( {$<[Measure]={'$(=Denominator)'},Country = P(external_country)>} [Measure_Value])
,
sum({$<[Measure]={'$(=Numerator)'}>} [Measure_Value])
/
sum( {$<[Measure]={'$(=Denominator)'}>}[Measure_Value]))
Any ideas on how I could structure the data model to fix this?
