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)
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.
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!!