Forum Discussion
Create a chart by isolating the multi-select values in the column
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
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?
5 Replies
- MarkLaf
Super User
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:
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
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?
