Group values together based on field value and create new combined value

[Anonymous]

Hi looking for a way to do this without creating a separate Table or a calculated Column. But looking for way to create a Measure that would combine a Sales Category for only certain Customers into a new combined value. See below, Lets lets say Customer Bill Gates has sales in multiple Sales Category and I'm looking to create a Measure that says" If Customer="Bill Gates" then combine Sales Category, else show Sales Category. So looking for 1. Ability to statically enter Certain\multiple  Customers to apply this to 2. Ability to statically list certain\multiple Sales Categories that I want to combine and the other Sales category will just be listed as normal.  3. Name this new comobined value any name that I want. Anyone have any ideas on how to do this with a measure? See example below. 

Before results:

Customer	Sales Category	Sales$
Bill Gates	Software	1,000
Bill Gates	Hardware	500
Bill Gates	Internet	200
Bill Nine	Science	200
Tiger Woods	Golf	99999
Tiger Woods	Promo	5

Expected Results: ( I would put in the Measure 2 things, Customer Name = "Bill Gates", and Sales Category to combine = "Software" and "Hardware" ) and have the new Combined Value be called "Combined" 

Customer	Sales Category	Sales$
Bill Gates	Combined	1,500
Bill Gates	Internet	200
Bill Nine	Science	200
Tiger Woods	Golf	99999
Tiger Woods	Promo	5

[amitchandak]

@Anonymous , in case you are dealing with column, you can have new category like

Switch(True(),
[Customer Name] = "Bill Gates" && [Sales Category] in {"Software" , "Hardware" }, "Combined",
[Sales Category]
)

else try binning or dynamic segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/

https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Greg_Deckler]

@Anonymous - Seems like:

Measure =
  VAR __Customer = MAX('Table'[Customer])
RETURN
  SUMX(FILTER(ALL('Table'),[Customer] = __Customer),[Sales$])


Sales Category Measure =
  VAR __Customer = MAX('Table'[Customer])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table),[Customer]=__Customer),[Customer],[Sales Category])
RETURN
  IF(COUNTROWS(__Table)>1,"Combined",MAX('Table'[Sales Category]))

[Anonymous]

@Greg_Deckler  Thanks but that almost worked, sorry I think I wasn't too clear on my first post of what I really need so let me clarify.  1. I need the ability to statically enter multiple Customers into the Measure that the Grouping will apply to and the others will be listed as normal.  2. I need to be able to statically enter multiple Sales Categories in the measure that will be combined and their sales because I don't want every Sales Category combined only certain ones.  See below.

Before results:

Customer	Sales Category	Sales$
Bill Gates	Software	1,000
Bill Gates	Hardware	500
Bill Gates	Internet	200
Bill Nine	Science	200
Tiger Woods	Golf	99999
Tiger Woods	Promo	5

Expected Results: ( I would put in the Measure 2 things, Customer Name = "Bill Gates", and Sales Category to combine = "Software" and "Hardware"  and have the new Combined Value be called "Combined" 

Customer	Sales Category	Sales$
Bill Gates	Combined	1,500
Bill Gates	Internet	200
Bill Nine	Science	200
Tiger Woods	Golf	99999
Tiger Woods	Promo	5

[Greg_Deckler]

@Anonymous - OK, maybe:

Measure Sales Category =
  VAR __Customers = { "Bill Gates" }
  VAR __Categories = { "Software", "Hardware" }
  VAR __Customer = MAX('Table'[Customer])
  VAR __Category = MAX('Table'[Category)
RETURN
  IF(__Customer IN __Customers && __Category IN __Categories,"Combined",__Category)

I'm thinking if you do that and use that as your Sales Category in your table visual then you will end up just needing a standard SUM Sales $ measure/aggregation.

[Anonymous]

@Greg_Deckler , this Measure only returns one of the Sales Category and also it totals all Sales Categories together as one when I add the Sales Amount.  also it does not create a new Combined Value like your first Measure did. 

[Greg_Deckler]

@Anonymous - OK, try this on for size. See attached PBIX, Table 8, Page 8

Measure Sales = 
  VAR __Customers = { "Bill Gates" }
  VAR __Categories = { "Software", "Hardware" }
  VAR __Customer = MAX('Table (8)'[Customer])
  VAR __Category = MAX('Table (8)'[Sales Category])
RETURN
  IF(__Customer IN __Customers && __Category IN __Categories,SUMX(FILTER(ALL('Table (8)'),[Customer]=__Customer&&[Sales Category] IN __Categories),[Sales$]),MAX([Sales$]))

[Anonymous]

@Greg_Deckler I tried this on my model and looked at your file sample and it doesn't give me the expected results when I remove the Original Sales Category field, which I would want to do it if I have a measure to replace it in the visual.  Also if there are mutliple Sales Category for same Customer it does not sum those it only selects the first instance of the Sales Category. I would like to use the original SUM measure of Sales$ field not a new measure if all possible. 

This is the results I'm looking for. 

Customer	Sales Category	Sales$
Bill Gates		5
Bill Gates	Combined	1500
Bill Gates	Internet	200
Bill Nine	Science	200
Tiger Woods	Golf	99999
Tiger Woods	Promo	5
Joel Embed	Baller	600
Joel Embed		11
Joel Embed	Dunker	555

[Greg_Deckler]

@Anonymous - OK, then just use this to create a new calculated column in your table:

Column Sales Category =
  VAR __Customers = { "Bill Gates" }
  VAR __Categories = { "Software", "Hardware" }
  VAR __Customer = [Customer]
  VAR __Category = [Category]
RETURN
  IF(__Customer IN __Customers && __Category IN __Categories,"Combined",__Category)

Then use this calculated column in your table visualization. Then you can use your original sum measure. 

[Anonymous]

@Greg_Deckler I need to do this  as a measure not a calculated column. Any other ideas?  The  previous suggestions came really close.  

[Greg_Deckler]

@Anonymous - Here is why that is not going to work.

1. You have your customer in your visual.
2. You have your measure in your visual.
3. You want your measure to return Combined when it is on a row for Hardware or Software (and a specific customer)
4. But, your measure has no context in which it can determine whether it is on a row for Hardware or Software so how would it possibly know whether to return "Combined" or the product value for the product row context which isn't there in the first place?
5. Answer, it cannot

Hence you are left with the following solutions:

1. Put the product context in the table/matrix and use the 2 measures presented earlier
2. Create a calculated column as shown and use your default sum measure
3. Figure out some way to magically introduce product into your row context that doesn't involve 1 or 2

I feel like I am trying to draw red lines with blue ink here. https://youtu.be/BKorP55Aqvg