Create a chart by isolating the multi-select values in the column

[VrowePPA]

I have a field in my table, where in my source data, products are selected from a multi select list of 9 items. eg:

Upgrade licences;Training;Technical Service Agreement

Upgrade licences;Training;Implementation;Technical Service Agreement

Upgrade licences;Training;Implementation

Upgrade licences;Training;Implementation

New licences;Training;Implementation;Technical Service Agreement

I'd like to create a visual like the one below, where I get a count of each of the nine items, eventhough they might appear in a string with other items.

In visuals, I can't figure out how to isolate the 9 values I want to see. Instead, I get a chart with every permutation/ combination of the values selected.

In the data table, I created 9 new columns with a function to populate the field if the value occurs. I thought I could create a chart out of these. But I get the same count for every column when I create a visual.

I used this formula to create each column. 

= Table.AddColumn(#"Added Custom8", "SaleTSA", each if Text.Contains([Sale Type], "Technical service agreement") then "Technical service agreement" else "")

What is the best way please to isolate and count my 9 products and show it on a chart like this one?

Here is my data: https://pythapartners.com.au/wp-content/uploads/2023/04/Example-products-table.xlsx

[MarkLaf]

You'll want to split your column by the ";" delimiter into a row for each product and then can do a simple row count by product.

Conveniently, this can be done through the Power Query UI without needing to write any M:

1) right-click on column header and select Split Column > By Delimiter...

2) select your delimiter ( ; ), select split at each occurence, and be sure to expand the Advanced options and change split into option to rows:

3) close and apply out of Power Query, now you can drop your column into the x-axis and y-axis of a bar chart. For the y-axis, Power BI will automatically convert this to an aggregation; for text columns, this aggregation should be count, which is what you want:

[VrowePPA]

Thank you @MarkLaf . That solutions works brilliantly to create the chart. But... it seems to also add to the total number of rows, which affects other sales stats. I'm guessing that if there are three delimited items, then there will essentially be the same sale, but in triplicate on three rows. For example: sample stats before making delimiter changes

And here is the same sample parameters but after applying the changes to get the chart to work.

Do you have any suggstions on how to address this?  Thanks again so far.

Sample Data: https://pythapartners.com.au/wp-content/uploads/2023/04/Example-products-table-2.xlsx

[MarkLaf · Accepted solution]

You will have to start building out your model into related dimensions and fact table(s). E.g. here is the Adventure Works model:

Your particular model will depend on your source data and business rules. If you have or can you create a Bids table where a single product is tagged at the transactional level, then you can use that for your product count and you will most likely want to relate your Bid table to a Product dimension table like shown above. Or does a single row in your transactional data have multiple products associated with it?

[VrowePPA]

Thank you @MarkLaf . I understand the idea you've supplied but it's a bit beyond my skill level at the moment. I'll work on it!  🙂  In the meantime, I did manage to make a series of measures which at least have created a visual, although of course it isn't too dynamic.

Thanks again.

[MarkLaf]

@VrowePPA, actually I'm only now realizing that you had provided some sample data, which makes it much easier for me to provide a more targeted recommendation. I believe you can achieve what you want with just one additional table.

To do this, create a new blank query, click advanced editor, then paste in the following:

let
    Source = Data, //Change Data to whatever name you have for your base table
    Select = Table.SelectColumns(Source,{"Sale Type"}),
    Rename = Table.RenameColumns(Select,{{"Sale Type", "Sale Type Group"}}),
    AddSplitTypes = 
        Table.AddColumn(
            Rename, 
            "Sale Type", 
            each Text.Split( [Sale Type Group], ";" ), 
            type {text} 
        ),
    ExpandSaleType = Table.ExpandListColumn(AddSplitTypes, "Sale Type"),
    Distinct = Table.Distinct(ExpandSaleType)
in
    Distinct

Note that the only thing you will probably have to tailor is the reference in Source - this should be the name of your base table in Power Query.

This will provide a table where it matches each group of products to applicable product rows:

Load both tables into the model and create the following relationship between them; in my example 'Data' is your main table and 'Sales Type' is the new table we've created:

Once you create the relationship, your model diagram should look like:

You can now drag and drop in a count of an ID column, Amount, or construct measure that will automatically be filtered by 'Sales Types'[Sale Type]: