Power BI Table Sums Filter Based on Pie Chart Selections

Question

UPDATED INFORMATION ABOUT RAW DATA

For the question below, I wasn't clear about the raw data and how the new dataset looks. The raw data is VERY raw, it looks something like this:

Team Name	Do you need supplies?
Sales	we could use some chairs for most people, new screens and probably keyboards, thanks!
Accounting	We'll take tables and chairs please!
Editing	Yes, chairs
Consulting	Please send us some tables, desks, screens, chairs, and keyboards, we're still getting set up

Then the dataset below is created by making calculated columns like this:

Chairs = SWITCH(TRUE(),
CONTAINSSTRING('Supplies'[Do you need supplies?], "Chair"), 1,
0)

Each flag column is a calculated column just like this one, so they don't show up in power query. Thank you! Sorry I wasn't clear about that before.

I have a pie chart that is made up of about 8 different calculated columns that are flags where the values are 0 or 1. For example, each column says "chairs", "tables", "desks", "screens", etc. The 0 vs. 1 is based on a column of team names, if the team needs the item in the column, there's a 1, else there's a 0, the data set looks something like this:

Team Name	Chairs	tables	desks	screens	keyboards
Sales	1	0	0	1	1
Accounting	1	1	0	0	0
Editing	1	0	0	0	0
Consulting	1	1	1	1	1

I've created a measure like this:

Total Supplies =
SUM ( Supplies[Chairs] ) + SUM ( Supplies[Tables] )
    + SUM ( Supplies[Desks] )
    + SUM ( Supplies[Screens] )
    + SUM ( Supplies[Keyboards] )

I've created a visual like this a pie chart of total counts of data next to table with team name and total counts of supplies each team needs :

The Pie Chart has each of the flag columns selected. The Table is the Team Name Column and the measure Total Supplies selected.

When I select the team name, I can see the pie chart filter what that team needs like this, sales is selected in the table and the pie chart is showing that sales needs chairs, screens, and keyboards :

Here is the issue: When I select a section of the pie chart, the numbers don't change on the table. I want the total supplies to change to only count the section(s) I have selected on the pie chart, like here I have selected tables, but the numbers in "Total Supplies" have not changed, the section of the pie chart that sums tables is selected, but the table has not responded to the filter :

Goal: I want it so when I click the Tables wedge that only Consulting and Accounting show 1, and the other teams show total supplies = 0

I've tried doing what I know how with creating a parameter with the flag columns and adding or taking away filters from the total supplies calculation, but I haven't been able to hit on anything yet that works.

Answer 1

Amira has the right idea unpivoting the table, however the raw data column needs to be split and unpivoted instead.

Input Table:

Team Name	Do you need supplies?
Sales	we could use some chairs for most people, new screens and probably keyboards, thanks!
Accounting	We'll take tables and chairs please!
Editing	Yes, chairs
Consulting	Please send us some tables, desks, screens, chairs, and keyboards, we're still getting set up

In Power Query:

Create New Query called GetWords

let Text_Substrings = (
    text as text,
    substrings as list,
    optional comparer as nullable function
) as list =>
    List.Select(substrings, each Text.Contains(text, _, comparer))
in
    Text_Substrings

Create a New Query called Words, this is the list of key words you want to look for.

let    
    Source = {"chair","screen","table", "desk", "keyboard"}
in 
    Source

Create a New Query called Table (or whatever you desire). This table will pull in your Supplies Table and pull out the key words from the Do you need supplies? column. These words will be put into a list and the list will be turned into rows.

let
    Source = Supplies,
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Do you need supplies?", "Text"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "items", each GetWords([Text], Words)),
    #"Expanded Check" = Table.ExpandListColumn(#"Added Custom", "items"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Check",{"Team Name", "items"})
in
    #"Removed Other Columns"

Your data now looks like this:

Team Name	Items
Sales	Chairs
Sales	screens
Sales	keyboards
Accounting	Chairs
Accounting	tables
Editing	Chairs
Consulting	Chairs
Consulting	tables
Consulting	desks
Consulting	screens
Consulting	keyboards

Create the following measure:

Total Supplies = COUNTROWS(Table)

Add the Team Name column from Table and Total Supply measure to a table.

Add the items column from Table and Total Supply measure to a Pie Chart.

Now when selecting the pie chart, the table will filter.

Answer 2

Teamwork Makes the Dreamwork! Thank you Amira and Nick for getting me on the right path! Your answers combined with this website: https://www.epcgroup.net/how-to-create-a-table-from-another-table-in-power-bi/

got me to the answer!

First, I went into the table editor and added a new table like this:

This is the code I entered for the new table to make it look pivoted like you both showed above:

Supply Counts = 
VAR _chair = SELECTCOLUMNS(Supplies, "Team Name", Supplies[Team Name], "Supply Needed", IF(Supplies[Chairs] = 1, "Chairs", BLANK()), "Count", Supplies[Chairs])

VAR _desk = SELECTCOLUMNS(Supplies, "Team Name", Supplies[Team Name], "Supply Needed", IF(Supplies[Desks] = 1, "Desks", BLANK()), "Count", Supplies[Desks])

VAR _keyboard = SELECTCOLUMNS(Supplies, "Team Name", Supplies[Team Name], "Supply Needed", IF(Supplies[Keyboards] = 1, "Keyboards", BLANK()), "Count", Supplies[Keyboards])

VAR _screens = SELECTCOLUMNS(Supplies, "Team Name", Supplies[Team Name], "Supply Needed", IF(Supplies[Screens] = 1, "Screens", BLANK()), "Count", Supplies[Screens])

VAR _tables = SELECTCOLUMNS(Supplies, "Team Name", Supplies[Team Name], "Supply Needed", IF(Supplies[Tables] = 1, "Tables", BLANK()), "Count", Supplies[Tables])

RETURN

UNION(_chair, _desk, _keyboard, _screens, _tables)

Now, I can make my Pie Chart with "Supply Needed" as the legend and "Sum of Count" as the values and I make my table with "Team Name" and "Sum of Count" and the filtering works!!

Thank you!!

Answer 3

You may need to change the way your table is designed. Instead of having separate columns for each supply item, you can transform your data so that each item type is represented as a separate row. Select the columns Chairs, Tables, Desks, Screens, Keyboards and select "Unpivot Columns" in Power Query :

Here is the query :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1W0lEyBGIDKDYE41idaCXH5OT80rySzLx0uDBCmQFYiWtKJpK8AYa8c35ecWkOmhEIHBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Name" = _t, Chairs = _t, tables = _t, desks = _t, screens = _t, keyboards = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team Name", type text}, {"Chairs", Int64.Type}, {"tables", Int64.Type}, {"desks", Int64.Type}, {"screens", Int64.Type}, {"keyboards", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Team Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Needs Items"}, {"Attribute", "Item"}})
in
    #"Renamed Columns"

Then create a measure to sum the Needs Items :

Total Supplies = SUM(Supplies[Needs Items])

and you can use it in both pie chart and table visuals :

You can see now that both visuals are interacting :