Data Categorisation in a new column

[vvibhakar]

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:

Month	Client	Sales
Jan	A	15,549
Jan	B	19,228
Jan	C	16,983
Feb	A	24,414
Feb	B	23,361
Feb	C	18,273
Mar	A	17,507
Mar	B	12,550
Mar	C	12,841
Apr	A	21,288
Apr	B	16,586
May	C	10,486
June	B	12,539
June	C	18,263
July	A	10,091
July	B	21,968
July	C	24,631
Total		296,558

Table 2:

A	88,849
B	106,232
C	101,477
Total	296,558

Table 3:

Sales Bucket

1) 10k - 25k

2) 25k - 40k

3) 40k - 60k

4) 60k - 75k

5) 75k - 100k

6) Above 100k

[Anonymous · Accepted solution]

@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 · Accepted solution]

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

[Anonymous · Accepted solution]

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

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

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

[vvibhakar]

I am not using any formula. I create a bar and line chart with the bucket as shared axis, sales value as bar/columns & then put client in line value and select Distinct count.
You formula gives me correct values, but it replicates on each single line if I use it for a column. If I use it as a measure, it adds up due to transactions count as well and does not show exact client counts per sales bucket in a visual.

Thanks

Vivek