Forum Discussion
SUM 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])
- SSWADOOD4 years ago
Helper I
i 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?
- SSWADOOD4 years ago
Helper I
changed the measure, displays the correct results in table visual but blank in card visual
- Anonymous4 years agoNot applicable
Hi SSWADOOD
I am not sure what went wrong, I got the dummy data, Pool and Status are two disconnected table, in the table visual there is column from Pool table, then the Card is showing the total...would you like to send me a sample .pbix file? I can message you my email address