Forum Discussion
Count and visualize data cells which contain several values (products)
Hi,
I have just started to use Power BI and kindly ask for some support regarding how to visualize and count cells that contain multiple values (products).
I have a table which shows different projects and the products that have been sold in this project. In addtion, the table contains many other columns such as e.g. the region. The column with the products sold contains cells with only one product and cells that contain multiple products separated by a semicolon (see example below)
I would like to show different reports and visualize the data. For example,
- to show which products have been sold in which region
- to show the products sold in a specific reporting period
- to show all the products that are already implemented
What is the best way to show achieve this?
I have tried to use the "split column by delimiter function into rows" (in Power BI query), but the problem is that all other rows and values are duplicated. This causes an issue with other visuals that I have created (e.g., bar chart to show how many projects we had in a reporting period / Or how many ongoing projects we have vs. implemented projects). As multiple rows are added for all the cells that contain multiple products, the data for the other columns is also duplicated and seems to be counted incorrectly for other visuals.
Maybe there is an easy solution to acheive what I want here, but I kindly ask for support!
| Status | Project | Products sold | Region | Reporting Period |
| Implemented | Project A | Product A | Northern Europe | Q1 |
Implemented | Project B | Product B; Product A | Southern Europe | Q2 |
| Ongoing | Project C | Product A; Product B; Product C | Western Europe | Q1 |
| Implemented | Project D | Product B | Western Europe | Q2 |
Hi,
Please read up on the CONCATENATEX() function.
Hi Anonymous ,
I suggest you to duplicate the [Products sold] in Power Query Editor and then split the duplicated one.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swtyEnNTc0rSU1R0lEKKMrPSk0uUXCEsFNKoWy//KKSjNSiPAXX0qL8glSgSKChUqwOLu1OSNqdrBWQTQrOL0U3yQhskn9een5mXjqSKc7IjkCYgmQgSEV4anEJ8S5zQXYZVt1A18QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, Project = _t, #"Products sold" = _t, Region = _t, #"Reporting Period" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}, {"Project", type text}, {"Products sold", type text}, {"Region", type text}, {"Reporting Period", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Products sold", "Products sold - Copy"), #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Status", "Project", "Products sold", "Products sold - Copy", "Region", "Reporting Period"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Reordered Columns", {{"Products sold - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Products sold - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products sold - Copy", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Products sold - Copy", "Products sold -Splited"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Products sold -Splited", Text.Trim, type text}}) in #"Trimmed Text"Then create a measure to achieve your goal.
Products 2 = IF(CONTAINSSTRING(MAX('Table'[Products sold]),";"),"Multi-product project",MAX('Table'[Products sold]))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi,
Splitting data by rows is the way to go. Once you do so, share the questions that you need answers to. Also, show the expected result.
Ok, I have done the splitting by rows. In my report view, I have several visuals where I show some data for the projects by country or status (e.g., bar chart: Y-axis --> country, X-axis --> Count of projects/id).
I have the following questions:
- I would like to show all projects by country but the total number of projects is incorrect --> I think I found the solution but can someone please confirm that the correct way is to count of ID to Count of ID (distinct)?
- I would like to include a table visual in the report to show detailed information for the different projects (including products). In this case, I would like the products not to be separated into different rows.
This is the current view:
ID Projects Products Region Opportunity value ID 117 Project X Product A Northern Europe 10000 € ID 117 Project X Product B Northern Europe 10000 € ID 4 Project D Product A Southern Europe 5000 € I would like to see the following view. So basically I need the same "cell value" again before the delimination where the products where combined. Otherwise, it the table shows the same projects multiple times with the only difference in the product column. Ideally, I would like to create another column (not existing) which shows "multi-product projects" for all projects including several products. Can I create this with a formula?
ID 117 Project X Product A, Product B multi-product project Northern Europe ID 4 Project D Product A Product A Southern Europe
3. I would like to show other visuals for example the total value by region (e.g., pie chart: Values --> Sum of opportunity value, Details: Region). But this visual shows now 20.000 € (Northern Europe) vs. 5000 € (Southern Europe) which is incorrect. Correct would be 10.000 € vs. 5000€. How can I avoid that these values are summed up for the addtional rows that are created by the delimination?
Apologies for all the questions. I have tried to look for some videos on this, but I don't even know exactly what to search for to describe what I would like to built. Hope with the explanation above it is clear. Thanks in advance for any support on my questions!
Hi,
For th e first one, write this measure
Number of projects = distinctcount(Data[Projects])
I cannot understand your second question.
For the third one, write these measures
Measure = min(Data[Opportunity value])
Measure1 = SUMX(VALUES(Data[Projects]),[Measure])
Use measure 1 in all your visuals where you want to see project wise totals.
Hope this helps.
Thank you for your response. Your solutions worked well, but I have another issue that emerged because I have slightly changed the table view. I have a column now which lists all the products sold for a specific project in one row like displayed below (Products combined). I would like to create another customized column which checks the products combined column and returns values based on the following:
- If products combined column is empty, return empty
- If products combined column contains exactly "Product A,Product B", return Product A including B
- If products combined column contains at least two of the following words/products (Product A, Product B, Product C, Product D), return Solution Project
- Otherwise return value of Products combined column.
I have tried to use the formula further down below, but apparently there is a mistake in the third IF statement and the result is not displayed correctly (see below in red). Would be great if you could advise what the mistake is here? If there is an easier way to achieve the expected result, please let me know. Thank you!
ID Products combined Product Reporting (current result) Product Reporting (expected result) 1 Product A Product A Product A 2 Product A,Product B Product A including Product B Product A including Product B 3 Product A,Product B,Product C, Product D Product A,Product B,Product C, Product D Solution Project 4 Product A,Product F Product A,Product F Product A,Product F IF( ISBLANK('Dashboard'[ProductsCombined]), BLANK(), IF( 'Dashboard'[ProductsCombined] = "Product A,Product B", "Product A including Product B", IF( CALCULATE( COUNTROWS( FILTER( VALUES('Dashboard'[ProductsCombined]), COUNTROWS( INTERSECT( VALUES('Dashboard'[ProductsCombined]), {"Product A", "Product B", "Product C", "Product D", "Product E", "Product F", "Product G"} ) ) ) ) ) >= 2, "Solution Project", 'Dashboard'[ProductsCombined] ) ) )