Forum Discussion

DylanEvans's avatar
DylanEvans
Frequent Visitor
3 years ago
Solved

Custom rollup calculation for measure

TL;DR: measure is correct at the lowest row, but rollup is wrong.  

 

I have a series of dependent measures that cross 3 intermediate 1:many tables. All tables have static data, but I'm using measures so users can select rows in the yellow table and see the resulting measure in the blue table.  The connecting measures (green, pink, and orange) use multiplying operations because I'm compounding probabilities of different scenarios. 

 

Measures calculations: 

  • threats (pink) adjusted_prob = product(threats[base probability]) * if(ISFILTERED('projects'),productx('projects-threats','projects-threats'[probability]),1)
  • threats-productrisks (green) adj_effect = product('threats-productrisks'[probability]) * productx('threats',[adjusted_prob])
  • product-risks (blue) this_cost = IFERROR(sumx('product-risks','product-risks'[cost]*[adj_effect]),0) 

The measure works correctly at the row-level of the blue table.  I would like to be able to group via a matrix visual by the two far-left tables, but the rollups are incorrect. I will never use the rollups of the intermediate orange, pink, or green measures; they are all the product of the rollups. For example, for the revenue orange rollup, it would be 0.0000079%=(.017*.035*.042*.0504*.063).  I would like rollups of the blue to be a (scaled) sum of the blue rows - in this case, maybe 13k. What happens instead is that the rollup is the same math, except applied only with the rollup numbers: almost 0.

 

 

 

As long as I'm dreaming big, I'd also like to scale the blue rollup, because the cost of several scenarios occuring is less than the sum of each individually happening.  maybe rollup = maxx(costs) * sum(1+ individual_cost/((.25 + indidual_cost/maxx(costs)).

Any ideas? 

3 Replies