Changing metrics structure without losing data


Userlevel 4
Badge +3


Changing the dimension of a metric in Pigment erases the input value within it. If your metric only has a formula and no metric override enabled, the changes are reversible though.
Here’s what you can do to make sure you keep all your input data is safe and not erased.

 

Before changing structure in multiple metrics, I highly recommend you take a snapshot of your application and the dependent ones to identify any data misalignment with no hassle.

 

Deciding on how you want to keep your data

 

If you want to add dimensions to your metric, the rule is probably an aggregation. For example, if you go from a metric with just months to a metric with months and countries, the data in months will aggregate across all countries.
If you go the other way around and remove a dimension, so going from a metric with both Month and Country to just Month, you have to decide on a rule for the allocation. There are simple rules you can go with.

  • If everything should go in a specific item you can go with 'Source Metric'[BY: Country."France"] which will allocate everything on the France country.
  • If you want to go 50%/40%/10% on your three countries, you should first create an 'Allocation Rule' metric (by Country) and type 'Source Metric'[ADD : Country] * 'Allocation Rule'

There are many other rules you can go with, and I suggest you make sure you try in the formula playground for every source metric you’ll change dimensions before proceeding.

Now that you know how you’ll aggregate / allocate your input data, you can change the metric structure.

I recommend changing the data structure of the metric you currently have rather than changing a copy, as it’s already being referenced in other formulas, and may have nice colors and formatting in your current boards. However, I suggest you try on a copy before you’re confident working with the real one.

 

Metric with no formula

 

  1.  Duplicate your metric to save the data on the side
  2. Change the structure of your original metric to the target structure dimensions
  3. Add a formula with the relevant allocation aggregation rule, e.g. 'Metric - copy'[BY: Country."France"] or 'Metric - copy'[ADD: Country] * 'Allocation Rule'
  4. Make sure your data is OK, e.g. by comparing it to your snapshot data or looking at totals
  5. Convert the formula to values. You can do that by right-clicking on the metric name (column or row) or from the Pivot panel, by clicking the three dots next to 'Metric label'
  6. Delete the copied metric, because you shouldn’t need it anymore.

You’re good to go!

 

Metric with a formula and metric override

Metrics with metric override enabled are a bit trickier to work with because you have two layers of data: the calculated values, and on top the input values. We’ll first separate both, then migrate the data, and finish with re-conciliating the input values and the formula.

  1. Work on your new formula first. Open a formula playground from the formula bar, and work your way towards your target. Working in the formula playground will ensure you only see the calculated values and not erase the precious input. Once you’re happy with your formula, save it somewhere.
  2. Duplicate your metric to save the data on the side
  3. Remove the formula in the copy, so that it only contains the input, e.g. overriden values.
  4. Change the structure of your original metric to the target structure dimensions
  5. Add a formula with the relevant allocation aggregation rule, e.g. 'Metric - copy'[BY: Country."France"] or 'Metric - copy'[ADD: Country] * 'Allocation Rule' to bring the input values from the copy to your original metric
  6. Make sure your data is OK, e.g. by comparing it to your snapshot data or looking at totals
  7. Convert the formula to values. You can do that by right-clicking on the metric name (column or row) or from the Pivot panel, by clicking the three dots next to Metric label
  8. Paste your new formula. If you display the visual hints, you should be able to see the inputs you’ve transferred in yellow and the new calculations in grey.
  9. Delete the copied metric, because you shouldn’t need it anymore.

You’re good to go!

 

Wrapping up

Make sure the metrics re-using your newly-changed metric can use the new structure. To do that, either use a [REMOVE SUM: Country], [SELECT: Country."France"], or add the dimension to the structure of the metrics you can see left of your block in the dependency diagram.


0 replies

Be the first to reply!

Reply