Forum Discussion
Helper ISUM BASED ON AMOUNTS RANGES
Hi SSWADOOD
I am a little bit lost, what is your desired result? Something like this? Changed the Policy_NO as I had to type all the data...
gp_status_sum =
VAR CurStatus = SELECTEDVALUE('Status'[Status])
RETURN
IF([GPLEVEL]=CurStatus,[GPSUM])
Helper Ii am trying to use the following measure to be used in a card visual
gp_status_sum =
var A = CALCULATE([GPSUM],GROSSPREM_CLASS[GROSS_PREM]>=1000000&&GROSSPREM_CLASS[GROSS_PREM]<=4999999)
var B = CALCULATE([GPSUM],GROSSPREM_CLASS[GROSS_PREM]>4999999)
var C = CALCULATE([GPSUM],GROSSPREM_CLASS[GROSS_PREM]<1000000)
var D = CALCULATE([GPSUM],GROSSPREM_CLASS[POOL] IN {"NON POOL"},GROSSPREM_CLASS[GROSS_PREM]=1000000&&GROSSPREM_CLASS[GROSS_PREM]<=4999999)
var E = CALCULATE([GPSUM],GROSSPREM_CLASS[POOL]IN {"NON POOL"},GROSSPREM_CLASS[GROSS_PREM]>4999999)
var F = CALCULATE([GPSUM],GROSSPREM_CLASS[POOL]IN {"NON POOL"},GROSSPREM_CLASS[GROSS_PREM]<1000000)
RETuRN
IF(SELECTEDVALUE('Status'[Status])="0 To 999,999"&&SELECTEDVALUE(POOL[POOL])= "WITHOUT POOL",F,
IF(SELECTEDVALUE('Status'[Status])="Above 5 Million"&&SELECTEDVALUE(POOL[POOL])= "WITHOUT POOL",E,
IF(SELECTEDVALUE('Status'[Status])="1,000,000 To 4,999,999"&&SELECTEDVALUE(POOL[POOL])= "WITHOUT POOL",D,
IF(SELECTEDVALUE('Status'[Status])="0 To 999,999",C,
IF(SELECTEDVALUE('Status'[Status])="Above 5 Million",B,
IF(SELECTEDVALUE('Status'[Status])="1,000,000 To 4,999,999",A,[GPSUM]))))))
what the measure is doing is that it sums the premium amount values of a policy which are less than and greater than the amount defined in the measure which it should not do, i want it to sum the amount of policies whose total amount ranges from 0 to 999,999, 5Million and above.
as there are some polices whose level does change when we remove the pool amount it goes from
"Above 5 million" to "1,000,000 to 4,999,999"
| POLICY_NO | CLASS | GROSS_PREM | POOL |
| EM/09-000919-12 | CMC | 0.00 | POOL |
| EM/09-000919-12 | ADMN | 0.00 | NON POOL |
| EM/09-000919-12 | MM | 4,249,520.87 | NON POOL |
| EM/09-000919-12 | MAT | 1,375,665.03 | NON POOL |
| EM/09-000919-12 | MMC | 650,000.00 | POOL |
| EM/09-000919-12 | HOS | 2,929,258.50 | NON POOL |
| total | 9,204,444.39 | ||
| without pool | 8,554,444.39 |
since all the values of this particular policy is less than 5 million its does not sum them ,
is there any way that i can filter the policies based on their total amount?