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

Question

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

greg_deckler · Answer

Anonymous&nbsp;- 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)&gt;1,"Combined",MAX('Table'[Sales Category]))

anonymous · Answer

Greg_Deckler&nbsp; Thanks but that&nbsp;almost worked, sorry I think I wasn't too clear on my first post of what I really need so let me clarify.&nbsp; 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.&nbsp; 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.&nbsp; See below.&nbsp;Before results:CustomerSales CategorySales$Bill GatesSoftware1,000Bill GatesHardware500Bill GatesInternet200Bill NineScience200Tiger WoodsGolf99999Tiger WoodsPromo5&nbsp;Expected Results:&nbsp;( I would put in the Measure 2 things, Customer Name = "Bill Gates", and Sales Category to combine = "Software" and "Hardware"&nbsp; and have the new Combined Value be called "Combined"&nbsp;CustomerSales CategorySales$Bill GatesCombined1,500Bill GatesInternet200Bill NineScience200Tiger WoodsGolf99999Tiger WoodsPromo5

greg_deckler · Answer

Anonymous&nbsp;- 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 &amp;&amp; __Category IN __Categories,"Combined",__Category)

&nbsp;
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.

amitchandak · Answer

Anonymous , in case you are dealing with column, you can have new category like 
&nbsp;
Switch(True(),[Customer Name] = "Bill Gates" &amp;&amp; [Sales Category] in {"Software" , "Hardware" }, "Combined",[Sales Category] )
&nbsp;
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-binshttps://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
&nbsp;

anonymous · Answer

Greg_Deckler&nbsp;, 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.&nbsp; also it does not create a new Combined Value like your first Measure did.&nbsp;

Forum Discussion

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

10 Replies