4 Replies Latest reply on Mar 29, 2018 11:54 AM by Eric Hammond

inner products, weighting, and scoring

Imagine I have a dataset that gives a weighting (impact factor) of deaths, based on the cause of death, a number of deaths, and a number of times that cause occurs without a death:

EVENT TYPE    |    W   |   deaths   |  events

car                    |     .1  |     5          |  8

car                    |     .1  |     3          |  6

animal               |    .2   |     2          |  10

human              |     .5  |     1          |   20

Now, for each event type, I want to take the sum(deaths)/(sum(events) + sum(death)) effectivelly giving a ratio or "death rate" - but this should be distinct for each death type.  Then I want to multiply that result for each event type's death rate by the weighting.  Finally, i want to some all those together.  So, given the above table, the explicit mathematical formula would look like:

Score = (.1)*(5 + 3)/(8+6) + .2(2/10) + .5(1/20)

I have tried a couple different things, but I'm always either mixing aggregates and non-aggregates, or summing the fractions individually (a/x + b/y instead of (a+b)/(x+y).

• 1. Re: inner products, weighting, and scoring

Hi Keegan,

• Create a calculated field, [Score]: ( SUM([Deaths]) / SUM([Deaths] + [Events]) ) * AVG(W)
• Drag [Event Type] to the rows shelf
• Drag [Measure Names] to the columns shelf
• Drag [Measure Values] to the Text tile on the Marks shelf
• From the menu, click Analysis, Totals, Show Column Grand Totals
• Right-click on a Score value and click format to add the desired number of decimal places.
• 2. Re: inner products, weighting, and scoring

Hi Keegan,

Have a look here. I have used your this Formula.

sum(deaths)/(sum(events) + sum(death))

Let me know, if it Helps.

Thanks

Deepak

• 3. Re: inner products, weighting, and scoring

Ok... I think this does produce the correct caclulation... however - is there a way to isolate the resulting number from the rest of the viz without destroying the order of operations?  Or is there  a way to reference the result from another worksheet?  Currently, it seems I would need to have the Event Type in the Rows for this to work.

• 4. Re: inner products, weighting, and scoring

[Score] = {FIXED [Event Type]: (SUM([Deaths]) / sum([Deaths] + [Events])) * AVG([W])}

[TotalScore] = {FIXED: SUM([Score])}

...or you can do it all in one calculated field:

{FIXED: SUM({FIXED [Event Type]: (SUM([Deaths]) / sum([Deaths] + [Events])) * AVG([W])})}