Hey all -
I am trying to calculate a weighted average for the metric 'LTV' across the dimension 'card type'. Their are three main card types; call them A, B and C. I would like to use 'cards sold' to derive the weight for each card type. Meaning, I'm dividing cards sold for each card type into the total cards sold across all three card types. See example below for a total of 60 cards sold across all three card types:
A = 10 cards sold, 10 / 60 = 0.16 weight
B = 20 cards sold, 20 / 60 = 0.33 weight
C = 30 cards sold, 30 / 60 = 0.5 weight
I would then take the weight and multiply it by the LTV for each card type, then sum those three numbers to get my final LTV metric. So that would look something like:
A: 15 LTV * 0.16 weight = 2.4
B: 20 LTV * 0.33 weight = 6.6
C: 30 LTV * 0.5 weight = 15
Total Weighted LTV Average = 24 (2.4 + 6.6 + 15)
I have tried using the AGGR function to create a temporary table of cards sold for each card type, but I'm not sure how I could divide each one into the total cards sold to derive the weight.
Anyone have any ideas how to create a function in the front end to do this?
Thanks in advance,