Weighting dimension values using custom metric

[MattBlake]

Hi, I have a bit of a tricky question which I find quite difficult to explain... I hope it makes sense!

Essentially, what I want to do is weight dimension values according to a calculated metric, but keep it so that each dimension item is weighted by an absolute value (the column sum) rather than relative numbers. So, for instance, what I want is something like:

eVar1|Metric 1|Metric 2|Weighting value = [Metric 1]/[Sum Metric 2]|Weighted Metric 2 = [Metric 2]*([Sum Metric 2] / [Sum Metric 1])

Sum|5000|20000|0.25|5000
Dimension 1|3000|10000|0.25|2500
Dimension 2|1500|5000|0.25|1250
Dimension 3|500|5000|0.25|1250

The problem I'm having is that the value in column D is automatically applied as a relative value (i.e. for dimension 1 it's 10000/3000) which means that [Sum Metric 2]/[Sum Metric 1] is 0.3, and therefore column D is [10000]*(0.3), i.e. 3000, thereby losing the weighting. 

Is there any way that anyone knows of doing this? The nearest I've come is using a function for the weighting value, using Mean[Sum Metric 2]/[Sum Metric 1], but the mean obviously isn't the same as the column sum... I'm hoping there's a nice simple solution I just haven't thought about

[Jennifer_Dungan]

Would something like this work?

Basically using nested functions (Column Sum) to get the total of your two metric columns, and nesting them in their own container to do the division? Then taking your metric and adding to the mix with a multiplication...

The "sample data" will look strange until you go back to your report with the dimension breakdown.... but it should work, since in your report, it should get the sums of your metrics as a whole (plus the division of those), then apply that multiplication to each dimension in the table by it's actual metric count.

[MattBlake]

Good thinking!

So what I've done now is followed your advice and used the column sum functions to basically create my weighting operator, so for instance:

Weighting operator = Column Sum (Metric 1) / Column Sum (Metric 2)
then
Metric 2 * Weighting operator.

This gives me a nicely weighted value for Metric 2, which adds up to (nearly the same) as Metric 1, but where if you add dimensions they're all weighted according to the overall Weighting operator rather than each having their own weighting operator applied. Which is extremely cool!

However, one slight glitch... if I use the column sum operator it gives me slightly higher figures than the column sums themselves.

So, for instance, if you create a metric for Column Sum (Visits), the number is slightly higher than the actual column total for the standard visits metric.

Could this be something to do with deduping?

[Jennifer_Dungan]

Oh, are you using a metric that potentially stacks? Like Unique Visitors or Visits? That would definitely cause a wrinkle in this....

[MattBlake]

Yep, using visits, that's the problem. Works brilliantly for Mobile Device Type, though

I may have to come up with a mirror version based on occurrences, see how that does...

[Jennifer_Dungan]

Yeah.. I wish there was a "de-dup" function for column sum... actually.. not a bad "idea" to post....

Here is a posted Idea that will hopefully get some traction:

https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-ideas/add-additional-controls-to-qu...

[AnicetAbreu]

Fantastic update so far. Don't hesitate to share more information about this one. Thank you!

[yuhuisg · Accepted solution]

You can also try changing the type of your "Metric 1", "Metric 2" in the Calculated Metric Builder to be "Total". See https://experienceleague.adobe.com/docs/analytics/components/calculated-metrics/calcmetric-workflow/...

[Jennifer_Dungan]

Actually, that setting works for me...  I removed the Column Sum function, and changed the metric to "Grand Total".... 

That is helpful @yuhuisg 

[MattBlake]

That does just the trick! Nice one - I'd never even seen that total option before