cancel
Showing results for
Did you mean:
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.

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

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.

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.
5 REPLIES 5
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.

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

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors