Slicers and aggregated tables?

[Betsy]

Hi,

I have data that I have to aggregate and then slice. I have achieved the aggregation, but not the slice.

Data looks something like this (simplified):

Table 1. Students

Student ID;Start Date;Gender;Active

1	9/1/2015	F	TRUE
2	1/15/2016	F	TRUE
3	10/1/15	M	FALSE

Table 2. Packages

Package ID;Student ID;Comm Type;Sent

1	1	question	9/6/2015
2	1	statement	9/7/2015
4	2	story	1/15/2016

Table 3. Responses

Package ID;Student ID;Text;Sent

1	1	Yes	9/6/2015
	3	Stop	10/1/2015
3	4	5	1/15/2016

I used SUMMARIZE on the packages table (in a new calculated table), to be able to calculate and report the total number of packages received, and I added a calculated column to find the percentage of students who received each package. Looks like this

Package ID; Total Received, Received Percentage

1                  10                   15%

I created a second calculated table to summarize Responses (Table 3) in the same way, then added a column to the first summarized package table using Related. Ended up like this:

Questions Received/Answered

Package ID; Total Received;Received Percentage; Total Responses; Percent Responses

1                 10                   15%                        2                       20%

However, I can't slice this data according to something like gender from the Student's table.

Is there another way to calculate this kind of aggregated percentage so I can have the same columns as Questions Answered/Received but can be sliced in a report? I've been trying GROUPBY in a calculated table, and I think I am close, I'm just not sure how to get each of these columns. If there is another way, I'd be happy to hear that as well (I'm just making this stuff up as I go along).

Thanks!

Betsy

[CahabaData]

I believe that by creating/relying on calculated tables that you make upfront a fixed value for the package, by which I mean it is written into a table, sort of like if the value was color blue. Just a stand alone value. Which is why it can't be sliced.

Instead trial a visual and don't use those calculated tables. Keep them as they could be useful in other ways - but for the visual you are making do not use those values. Assuming you have a join from Students to Packages - then recreate the % received, etc as a Measure in your visual set (rather than the data set where is calculated tables). This dynamic Measure formula is part of the visual data set and I believe should allow you to slice.

[Betsy]

@CahabaData

That makes sense.

I have created measures where I can create a visual for Received/Answered totals and percentages, for a given package ID and those do slice. But that is tedious (there are almost 600 of each), and (even more important), I have to dig elsewhere in our database to see what population got a particular question, and then I have to calculate the measure to see if it would be worthwhile to report or not.

What I want is a list of questions pretty much exactly like my static list of Questions Received/Answered but that I can click on a slicer and say, "Oh, women received and answered question 1, let me create a visual of their responses for question 1. Men received and responded to question 2, etc."

Is there a way to do that?

Thanks a million everyone. 🙂

Betsy

[CahabaData · Accepted solution]

We are not quite in sync when you state: create a visual for Received/Answered totals......for a given package ID

If table 2 represents packages then 1 Measure would apply to all package IDs, there could be 600 for sure but I'm not understanding how/why one is making a measure for a single package ID.

The further topic on a measure per question; so far the tables have been on students and packages - there is nothing I see that indicates a question # (ID) as part of the data set.  But like I say - it may be that I am just not in sync with your explanation.

In general I think with the right hierarchy and relationship joins - what you seek can be accomplished with Power BI.  But not 100% certain of the data model so difficult to be more precise.

[Betsy]

Some of the packages are questions (there is a column in the Package Table that lists communication type). The only packages I'm interested in looking at (for this anyway) is questions, so I made the calculated table summarizing just questions (whose ID numbers are in the Package ID column from the Package Table and from the Package ID column in the Response Table to give me Received and Answered).

I've always thought of measures as something that gives one aggregated result. So, to look at one specific question (or Package ID) in a report, the way I have it set up now, I can filter on that Package ID on my Questions Received/Answered table, or I could use two fields and two measures:

1. Field 1: Package ID from a pivot of Package IDs from the Package Table on Student IDs from the Student Table. That way I have a received/not received for each Package ID (question). I can then put this Package ID into a report and sum or count on the field. I get Total Received.

2. Measure 1: SUM field 1/student count (from student table) to get Percent Received.

3. Field 2: Package ID from a pivot of Package IDs from the Response Table on Student IDs from the Student Table. I get Total Answered.

4. Measure 2: [Total Answered]/[Total Received].

When the data isn't sliced, the numbers from the summarized table and from the four values in a visualization for a given package ID correspond. When I add a slicer, the second method does slice, but not the first (maybe because it is not dynamic or because I don't have the relationships set up correctly as stated above). It actually works pretty nicely this way, because I can have the overall numbers displayed at the same time as the subpopulation numbers. But, I would like the same table as above with received/replied and percentages that does slice, so I can see what any given subpopulation actually got, and replied to, then I can look at their actual replies (the next component of the report I'm creating, which I'm having no trouble with).

[Betsy]

Ok, I got it I think. I made a bridge table with disinct Package IDs, set up a relationship between this bridge table and both Package Table and Response Table, and lined up a Question Total, Question Percentage, Response Total, and Response Percentage in a visualization, which are all calculated measures, with Package IDs from the Package Table, which has a relationship with the Student Table. Now the totals and percentages slice with student table fields.

Thanks everyone for helping me think through it!

[v-haibl-msft]

@Betsy

Did you create relationship between Students and Packages with Student ID, Packages and Questions Received/Answered with Package ID?

Best Regards,

Herbert

[Betsy]

@v-haibl-msft

I do have relationships there. When I slice, the relevant package IDs are shown (so I get a subset of the total Package IDs), but the totals and percentages remain at the overall population level. 

I was thinking maybe to GROUPBY instead of SUMMARIZE to get the same numbers, and those total and percentages may slice? 

[cosborn1231]

If you can't slice it is most likely a relationship issue between your tables.

Make sure all you tables are linked by Student ID.

I would suggest you write some measures instead of calculated tables or columns here.