Count and visualize data cells which contain several values (products)

[LucaN123]

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

[Ashish_Mathur · Accepted solution]

Hi,

Please read up on the CONCATENATEX() function.

[LucaN123]

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]
        )
    )
)

[Ashish_Mathur]

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.

[LucaN123]

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: 

1.  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)? 
2. 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!

[Ashish_Mathur]

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.

[LucaN123]

Hi, 

Thanks for your answer to Question 1 + 3 - this works perfectly. 

I will try to explain Question 2 in more detail - there are actually two sub-questions for this. 

Question 2.1: Before I've separated the Products column by delimiter into rows, the product column contained both cells with only one product & multiple products in one cell. 

In the report, I have used a "table visual" to show the information for the different projects and which products are included (see below). You could easily see if a project includes multiple products. As the products are now separated into multiple rows, the "table visual" also shows several rows for the same project (see current view). Is it possible to combine the different products again in one cell? Please note that I would still like to have the rows separate to count the products in other visuals. I would assume I need to create another column again in the table view which combines all products again in one cell (then I could use this column in the table visual instead). 

Expected view: 

ID	Projects	Products	Region	Status
ID 1	Project X	Product A; Product B; Product C	Northern Europe	Ongoing
ID 2	Project Y	Product B	Northern Europe	Implemented

Current view: 

ID	Projects	Products	Region	Status
ID 1	Project X	Product A	Northern Europe	Ongoing
ID 1	Project X	Product B	Northern Europe	Ongoing
ID 1	Project X	Product C	Northern Europe	Ongoing
ID 2	Project Y	Product B	Northern Europe	Implemented

Question 2.2: 
I would like to create another column or measure in my table view which shows if projects contain only one product or multiple products. If a project contains multiple products, the cell should display "Multi-product project". If a project contains only one product, the cell should display the product (see below in blue).

ID	Projects	Products	Products 2
ID 1	Project X	Product A; Product B; Product C	Multi-product project
ID 2	Project Y	Product B	Product B

I hope you understand the question now. Otherwise, please let me know. I think the challenge is that I need to split the product column into separate rows to count and visualize the products in a better way. But for other visuals, I would need the column which contains all products combined in the cell. 

Thanks in advance.

[v-rzhou-msft · Accepted solution]

Hi @LucaN123 ,

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Please read up on the CONCATENATEX() function.