Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@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
@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]))
@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 |
@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.
@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.
@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$]))
@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 |
@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.
@Greg_Deckler I need to do this as a measure not a calculated column. Any other ideas? The previous suggestions came really close.
@Anonymous - Here is why that is not going to work.
Hence you are left with the following solutions:
I feel like I am trying to draw red lines with blue ink here. https://youtu.be/BKorP55Aqvg
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |