Is it possible to summarize by a Measure?

[nicolamullins]

I am trying to recreate a solution that I had previously developed in Tableau (we are converting all dashboards to power BI currently. 

I have a dataset that includes assessment information for clients. so contains information like

client ID, date of assessment, Iteration, question 1, answer 1

client ID, date of assessment, Iteration,  quesiton 2, answer 2 etc...

I then calculated the variance of their average assessment scores by iteration. So a single client may have an intake, 90 days, and closure assessment on file, and we want to see how the average scores changed from intake to 90 day, intake to closure etc. I have successfully calculated these variances in individual columns. 

I created a disconnected slicer with the different options - intake to 90, intake to closure etc. which I use in a measure to get the correct variance score by calling the correct variance column based on the user's selection. Using that #, I have a measure that is calculating whether or not that value is an improvement.

Now I am trying to aggregate this data up to a program level across all clients and I am running into issues. I can't get it to show the # of clients that improved their score based on the variance that was selected. I have attached a screen shot of what I am getting. The bottom table correctly has the variance for intake to latest iteration for each client enrollment, and it is correctly calculating whether or not that client improved or not. In the top table, I want to show how many cases had a '1' in the # Cases with Improvement column by program type. I have tried the SUMMARIZE function, but it doesn't like that I am trying to summarize by a measure, and not a column. Because the values change based on the slicer selection, everything I've tried gives me an error. 

Any help would be appreciated! I am stumped here. 

[Greg_Deckler]

@nicolamullins Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[nicolamullins]

@Greg_Deckler 

Thank you for the links, I have not been able to find a solution to my problem on the community yet. 

Here is some sample data for a single client. 

This client has 3 assessment iterations on file, intake, 90 days, and closure. The avg assessment score is the average of all scores for a single iteration. The variation columns are the difference between the average scores of two iterations. For example, variation (intake to 90 Days) is calculated as (avg assessment score for 90 days) - (avg assessment score for intake).

client id	Program	iteration	question	score	Avg Assessment Score	variation (intake to 90 days)	variation (intake to closure)
1234	Casework	Intake	question 1	1.5	1.55	0.55	-0.55
1234	Casework	Intake	question 2`	1.6	1.55	0.55	-0.55
1234	Casework	90 Days	question 1	2.3	2.1	0.55	-0.55
1234	Casework	90 Days	question 2	1.9	2.1	0.55	-0.55
1234	Casework	Closure	question 1	1.1	1.0	0.55	-0.55
1234	Casework	Closure	question 2	0.9	1.0	0.55	-0.55

On my dashboard, I have a disconnected slicer that allows the user to choose which variance they want to analyze. In this case, I have Intake to 90 Days selected. I also have a measure called Variance (Selected) that grabs the data from the correct variance column based on the users selection 

   

So with this data, I built a basic table with client IDs that breaks out the variance by case/program for a client. The Variance (Selected) Column shows the variance for whichever option the user chooses. 

I then created the # Cases with Improvement measure to determine if a case improved or not - if the variance >= 0.01, then it improved (1) else it didn't improve (0). As you can see below, this is all working so far. 

The problem I have now is I want to summarize the number of cases that improved by other columns. For example, I am trying to build a table that shows # of cases improved by program type. If I had 150 casework cases with assessments on file, and 25 of them improved, I want to see 25 in that column. 

I think I need to do something with SUMX and/or summarize maybe, but I haven't been able to get anything to work so far. 

Please let me know if you have any questions, I appreciate your help!

[nicolamullins · Accepted solution]

I figured out a solution to this - not elegant, but it works. 

I created a new column for each variance option that calculates at the case level if that case improved for the variance selected. I then have a measure that picks the max value for that case based on the variance slicer selection. Finally, I have a 2nd measure that sums up the 1 or 0 for each case to get to the final totals that I needed. Lots of added columns and measures, but it works.