Forum Discussion

vvibhakar's avatar
vvibhakar
Frequent Visitor
8 years ago
Solved

Data Categorisation in a new column

Hello Everyone,

 

I want to have dynamic column which can group my client based on the sales. For example in below Table 1, I have my Client sales Value by month. I can easily represent this as well as the total of each clients (Table 2).

 

What i actualy want, is a Virtual Column which can put this clients in groups/buckets according to thier sales. For eg. Client A falls in "75k - 100k" and Clients B & C falls in "Above 100k". The values in the columns should change when I filter out months. For e.g. if i select 1st quarter of the year, all the Clients should fall under "40k - 60". Refer Table 3 for the groups.

 

Thanks in Advance.

 

Table 1:

MonthClientSales
JanA15,549
JanB19,228
JanC16,983
FebA24,414
FebB23,361
FebC18,273
MarA17,507
MarB12,550
MarC12,841
AprA21,288
AprB16,586
MayC10,486
JuneB12,539
JuneC18,263
JulyA10,091
JulyB21,968
JulyC24,631
Total 296,558

 

Table 2:

A88,849
B106,232
C101,477
Total296,558

 

Table 3:

Sales Bucket
1) 10k - 25k
2) 25k - 40k
3) 40k - 60k
4) 60k - 75k
5) 75k - 100k
6) Above 100k
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    vvibhakar You don't need 3 tables. All you need are 2 calculations.

     

    Sales Per Client = 

    CALCULATE(SUM(Table1[Sales]), ALLEXCEPT(Table1,Table1[Client]))

     

    Sales Bucket Per Client = SWITCH(TRUE(),

                                             Sales Per Client >= 10000 && Sales Per Client <25000,"10-25K",

                                             Sales Per Client >= 25001 && Sales Per Client <40000,"26-40K",

                                             Sales Per Client >= 40001 && Sales Per Client <60000,"41-60K",

                                             "60+")

     

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi vvibhakar,

     

    You could creata a measure like below.

     

    group =
VAR a =
    SUM ( 'Table'[Sales] )
RETURN
    IF (
        ISBLANK ( a ),
        BLANK (),
        IF (
            AND ( a > 10000, a < 25000 ),
            "10K-25K",
            IF (
                AND ( a > 25000, a < 40000 ),
                "25K-40K",
                IF (
                    AND ( a > 40000, a < 60000 ),
                    "40K-60K",
                    IF (
                        AND ( a > 60000, a < 75000 ),
                        "60K-75K",
                        IF ( AND ( a > 75000, a < 100000 ), "75K-100k", "Above100K" )
                    )
                )
            )
        )
    )

    Here is the output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    vvibhakar You don't need 3 tables. All you need are 2 calculations.

     

    Sales Per Client = 

    CALCULATE(SUM(Table1[Sales]), ALLEXCEPT(Table1,Table1[Client]))

     

    Sales Bucket Per Client = SWITCH(TRUE(),

                                             Sales Per Client >= 10000 && Sales Per Client <25000,"10-25K",

                                             Sales Per Client >= 25001 && Sales Per Client <40000,"26-40K",

                                             Sales Per Client >= 40001 && Sales Per Client <60000,"41-60K",

                                             "60+")

     

    • vvibhakar's avatar
      vvibhakar
      Frequent Visitor
      8 years ago

      Anonymous
      This works great. But I still require a line over these buckets where i need my count of unique Clients in each bucket. The Unique count of either Clients or the Sales bucket per client displays wrong values.

      Can you also please tell me which way can i have that.

      Thanks
      Vivek

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        vvibhakar what is the formula you are using for the unique count of clients per bucket?

         

        it should be something like

         

        CALCULATE(DISTINCTCOUNT(Table1[Client]), ALLEXCEPT(Table1, Table1[Sales Bucket Per Client])

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi vvibhakar,

     

    You could creata a measure like below.

     

    group =
VAR a =
    SUM ( 'Table'[Sales] )
RETURN
    IF (
        ISBLANK ( a ),
        BLANK (),
        IF (
            AND ( a > 10000, a < 25000 ),
            "10K-25K",
            IF (
                AND ( a > 25000, a < 40000 ),
                "25K-40K",
                IF (
                    AND ( a > 40000, a < 60000 ),
                    "40K-60K",
                    IF (
                        AND ( a > 60000, a < 75000 ),
                        "60K-75K",
                        IF ( AND ( a > 75000, a < 100000 ), "75K-100k", "Above100K" )
                    )
                )
            )
        )
    )

    Here is the output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry