Forum Discussion

snandy2011's avatar
snandy2011
Helper IV
8 years ago
Solved

how to can i create a group within a specific range based on other column

Hi everyone,

 

I have datasets which has profit column and gaming account column. i Have to create a coulmn based on gaming account which will be a specfic range. For expample, suppose i have data like

 

Gaming Account   Profit

 A                             500

 B                             200

 A                             200

 C                             500

 A                             300

 C                             800

 

Now, i want to group these customers like,

 

Customer Group    Min- Profit     Max Profit

 Top Customer        1000               1500

 OK Customer         500                  1000

Small Customer      0                      500

 

Now, Over here A's over all profit is 1000, so, he will be in to top customer group..B's profit is 200, so B is Small customer and C is again Top customer. So, my desired output will be,

 

 

Gaming Account   Profit        Account Segment

 A                             1000      Top customer

 B                              200        Small Customer

 C                             1300       Top Customer

 

 

I have tried this by following DAX way, but no luck..

 

Measure= CALCULATE([Total Profit],FILTER(VALUES('Profit'[Gaming Account]), IF( 'Profit'[Gross Gaming Revenue] <=500,"Small Customer",IF('Profit'[Gross Gaming Revenue]<=1000,"Ok Customer","Top Customer"))))

 

 Can you please help me on that??  how to achieve this stage.

 

Any suggesation is really appreciable.

 

Thanks,

snandy2011

 

 

 

 

 

  • Hi,

     

    Try these measures

     

    [Total Profit]=SUM(Table1[Profit])

     

    [Account Segment]=if(HASONEVALUE(Table1[Gaming Account]),if([Total Profit]>=1000,"Top Customer",if([Total Profit]>=500,"OK Customer","Small Customer")),BLANK())

     

    Hope this helps.

     

3 Replies

  • Hi,

     

    Try these measures

     

    [Total Profit]=SUM(Table1[Profit])

     

    [Account Segment]=if(HASONEVALUE(Table1[Gaming Account]),if([Total Profit]>=1000,"Top Customer",if([Total Profit]>=500,"OK Customer","Small Customer")),BLANK())

     

    Hope this helps.