Weighting dimension values using custom metric | Community
Skip to main content
M
MattBlake
October 21, 2022
Solved

Weighting dimension values using custom metric

  • October 21, 2022
  • 3 replies
  • 2578 views

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 🙂

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by yuhuisg

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/m-metric-type-alloc.html?lang=en#metric-type

3 replies

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
October 21, 2022

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.

    M
    MattBlakeAuthor
    October 24, 2022

    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
    Community Advisor and Adobe Champion
    Jennifer_DunganCommunity Advisor and Adobe Champion
    Community Advisor and Adobe Champion
    October 24, 2022

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

    A
    AnicetAbreu
    October 22, 2022

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

    yuhuisg
    Community Advisor
    yuhuisgCommunity AdvisorAccepted solution
    Community Advisor
    October 24, 2022

    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/m-metric-type-alloc.html?lang=en#metric-type

      Jennifer_Dungan
      Community Advisor and Adobe Champion
      Jennifer_DunganCommunity Advisor and Adobe Champion
      Community Advisor and Adobe Champion
      October 24, 2022

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

       

      That is helpful @yuhuisg 

        M
        MattBlakeAuthor
        October 24, 2022

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

          Terms & ConditionsAccessibility statement

          Loading footer...