cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vvibhakar
Frequent Visitor

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
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@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+")

 

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

group.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

group.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

@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+")

 

View solution in original post

@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
Not applicable

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

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors