cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterStuhr Regular Visitor
Regular Visitor

Combine Values into groups

Hello,

 

We have a table containing "Job ID", "Job Type" and "Revenue" (and a lot more - not important).

The problem is we have 30+ job types, and we would like to put them into 4 groups.

 

The goal is to look at revenue per job type - so instead of revenue per 30+ job types, we would like to see the revenue per 4 combined types.

 

So lets say Job type 1-10 Should belong to "Jop Type A", Job type 11-20 should be "Job Type B" etc.

 

Is there an easy way for us to do that in this huge data table?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Gordonlilj Member
Member

Re: Combine Values into groups

Right, 

Creating a group following the link i posted before should work fine

Use grouping and binning in Power BI Desktop

In short, right click on the column -> New group, in group type choose list and than group them in any way you like

7 REPLIES 7
Super User
Super User

Re: Combine Values into groups

@PeterStuhr  Please post sample test data to suggest an accurate solution. But from the lines of your requirement, I suggest to create a Rank field which will give you the rank based on their Revenue and then use this Rank field to create a groups using DAX



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Gordonlilj Member
Member

Re: Combine Values into groups

Hi,

 

You could try and create a group:

 Use grouping and binning in Power BI Desktop

But as PattemManohar 

 

 

PeterStuhr Regular Visitor
Regular Visitor

Re: Combine Values into groups

Sample data:

 

Job IDJob TypeRevenueNEW
1Banana10Fruit
2Apple4Fruit
3Grape10Fruit
4Red100Wine
5White200Wine
6Rosé150Wine
7Red100Wine
8Banana10Fruit
9Apple10Fruit
10Grape10Fruit
11T-shirt200Clothes
12Pants230Clothes
13Pants250Clothes
14T-shirt200Clothes
15Banana10Fruit
16Apple5Fruit
17Grape7Fruit
18Red120Wine
19White200Wine
20Rosé350Wine
21Banana10Fruit
22Apple5Fruit
23Grape5Fruit
24T-shirt100Clothes
25Pants250Clothes

 

So the "NEW" column is how I think it could be Smiley Happy

Super User
Super User

Re: Combine Values into groups

Thanks @PeterStuhr  for sample data. 

 

Is NEW column is your expected output ? or that is what need to considered for JobType to calculate Revenue - Please confirm.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Highlighted
Super User
Super User

Re: Combine Values into groups

@PeterStuhr  Hope this is what you are after..

Create a New Table as below

 

Test310Out = SUMMARIZE(Test310Groups,Test310Groups[JobType],"Revenue",SUM(Test310Groups[Revenue]))

Then, add Rank field to the above table as below

Rank = RANKX(Test310Out,Test310Out[Revenue],,DESC,Dense)

Finally, add a Grouping field based on the Revenue (which we have assigned Rank in above step)

Group = IF(Test310Out[Rank]<=5,"Type A",IF(Test310Out[Rank]<=10,"Type B","Other"))

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Gordonlilj Member
Member

Re: Combine Values into groups

Right, 

Creating a group following the link i posted before should work fine

Use grouping and binning in Power BI Desktop

In short, right click on the column -> New group, in group type choose list and than group them in any way you like

Super User
Super User

Re: Combine Values into groups

@Gordonlilj  In this case we need to group based on the total revenue (which is another field).  So can't use normal grouping option.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !