Expand my Community achievements bar.

Join us at Adobe Summit 2024 for the Coffee Break Q&A Live series, a unique opportunity to network with and learn from expert users, the Adobe product team, and Adobe partners in a small group, 30 minute AMA conversations.
SOLVED

Weighting dimension values using custom metric

Avatar

Level 2

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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/...

View solution in original post

9 Replies

Avatar

Community Advisor

Would something like this work?

Jennifer_Dungan_0-1666388597728.png



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.

Avatar

Level 2

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?

Avatar

Community Advisor

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

Avatar

Level 2

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...

Avatar

Community Advisor

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...

 

Avatar

Level 2

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

Avatar

Correct answer by
Community Advisor

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/...

Avatar

Community Advisor

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

 

That is helpful @yuhuisg 

Avatar

Level 2

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