Hi there,
Apologies if this is a fairly basic request but I'm quite new to building DAX expressions.
I'm trying to create a measure to calculate the reinsurance premium from two tables which I've posted at the bottom.
Reinsurance Premium is caluculated using the equation: [Original Premium]*0.8*[Share of Contract]
The idea of the measure is create a situation where if a user were to filter on one of the reinsurers in Table 2, then they will be shown the total reinsurance premium assosiated with that insurers shares across all years (or a particular year if the user selects this).
My issue is I can get the [Original Premium] to multiply by the sum of the entire [Share of Contract] column in Table 2, but I can't limit it to just multiply according to the total of the relevent year's [Share of Contract] (which should always total 1 when the dashboard is unfiltered).
Whats happening now (as if unfiltered and showing all years):
| Original Premium | 200 |
| Equation | 200*0.8*2 (2 is the total for the [Share of Contract] column for both years) |
| Reinsurance Premium | 320 (Twice what it should be) |
What should be happening:
| Original Premium | 200 |
| Equation | 100*0.8*1 (1 is the total of [Share of Contract] for 2017) + 100*0.8*1 (1 is the total of [Share of Contract] for 2018) |
| Reinsurance Premium | 160 |
I may not have explained this too well, but if you've got any idea what I'm on about and can help then I'd appreciate it.
Cheers!
Table 1:
| Original Premium | Year |
| 100 | 2017 |
| 100 | 2018 |
Table 2:
| Year | Reinsurer | Share of Contract |
| 2018 | Aspen Re | 0.208800 |
| 2018 | AXA XL Bermuda | 0.002113 |
| 2018 | AXA XL Syndicate | 0.043373 |
| 2018 | Everest Re | 0.118098 |
| 2018 | MS Amlin Syndicate | 0.203352 |
| 2018 | Partner Re | 0.148871 |
| 2018 | Ren Re | 0.044449 |
| 2018 | Scor | 0.211812 |
| 2018 | Trans Re | 0.019133 |
| 2017 | Aspen Re | 0.011959 |
| 2017 | AXA XL Bermuda | 0.061815 |
| 2017 | AXA XL Syndicate | 0.054838 |
| 2017 | Everest Re | 0.105854 |
| 2017 | MS Amlin Syndicate | 0.174294 |
| 2017 | Partner Re | 0.140202 |
| 2017 | Ren Re | 0.263262 |
| 2017 | Scor | 0.059229 |
| 2017 | Trans Re | 0.128547 |
