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?
Solved the issue in the following way:
sum({$<[Measure]={'$(=Numerator)'}, Country = {"=SUM({<[Measure]={'$(=Denominator)'}>} [Measure_Value])>0"}>}[Measure_Value])
/
sum({$<[Measure]={'$(=Denominator)'}, Country = {"=SUM({<[Measure]={'$(=Numerator)'}>} [Measure_Value])>0"}>}[Measure_Value])