Forum Discussion

SSWADOOD's avatar
SSWADOOD
Icon for Helper I rankHelper I
4 years ago

SUM BASED ON AMOUNTS RANGES

I need help with some dax measures,

currently i have a table with gross_premium of policies  which divided by classes of pool and non pool

First i wanted to get sum of premium of each policy with and without pools, and created the following measure

GPSUM = 
VAR A = CALCULATE(SUM(GROSSPREM_CLASS[GROSS_PREM])+0)
VAR B = CALCULATE(SUM(GROSSPREM_CLASS[GROSS_PREM])+0,GROSSPREM_CLASS[POOL] IN {"NON POOL"})
RETURN
IF(SELECTEDVALUE(POOL[POOL])= "WITHOUT POOL",B,A)

i also have a measure which shows ranges of polices according to their total amount, which will be used as a filter

GPLEVEL = 
IF([GPSUM] >= 1000000 && [GPSUM]<= 4999999,"1,000,000 To 4,999,999",
                        IF([GPSUM] > 4999999, "Above 5 Million",
                                                "0 To 999,999") )

 i am trying to get a sum of policies based on total ranges. using the following measure

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]))))))

 

but i am un able to get the desired result, figured out that

GROSSPREM_CLASS[GROSS_PREM]>=1000000&&GROSSPREM_CLASS[GROSS_PREM]<=4999999
GROSSPREM_CLASS[GROSS_PREM]>4999999)
GROSSPREM_CLASS[GROSS_PREM]<1000000)

,GROSSPREM_CLASS[POOL]IN{"NON POOL"},GROSSPREM_CLASS
[GROSS_PREM]>=1000000&&GROSSPREM_CLASS[GROSS_PREM]<=4999999
,GROSSPREM_CLASS[POOL]IN{"NON POOL"},GROSSPREM_CLASS[GROSS_PREM]>4999999)
,GROSSPREM_CLASS[POOL]IN{"NON POOL"},GROSSPREM_CLASS[GROSS_PREM]<1000000)

 is the issue.

 

is there a way to fix this, i need to group the sum of the premium (with or without pool) on the policy_no 

 

as their are policies whose range changes when filtered with or without pool

 

 

 

thanks  

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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])

     

     

    • SSWADOOD's avatar
      SSWADOOD
      Icon for Helper I rankHelper I
      4 years ago

      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_NOCLASSGROSS_PREMPOOL
      EM/09-000919-12CMC0.00POOL
      EM/09-000919-12ADMN0.00NON POOL
      EM/09-000919-12MM4,249,520.87NON POOL
      EM/09-000919-12MAT1,375,665.03NON POOL
      EM/09-000919-12MMC650,000.00POOL
      EM/09-000919-12HOS2,929,258.50NON POOL
       total9,204,444.39 
       without pool8,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?

    • SSWADOOD's avatar
      SSWADOOD
      Icon for Helper I rankHelper I
      4 years ago

      changed the measure, displays the correct results in table visual but blank in card visual

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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

         

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi SSWADOOD 

    I notice that this post is inactive recently. Have you solved this question with Vera_33's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.