Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate and filter not working

Hello All,    I am running some survey stats and have a simple aggregate measure. I am looking to calculate the number of codes in the dataset which have less than 70% respondent agreement. I am us...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    In that case.....Try Replacing allselected with VALUES

     

    % Agree <70 =
COUNTROWS ( FILTER ( VALUES ( Sheet1[course_code] ), [11 % Agree Col] < .7 ) )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks Guys, 

 

This didnt work. 

 

my Data is structured like this:

 

Respondent ID | Category | Code | Agree or Disagree

 

e.g. 

123455| MED | 1XY45 | Agree

123344| MED | 1XY45 | Disagree

 

 

I want to be able to calculate % Agreement of each code (in this case 1XY45) and then count the number of codes that have less than 70% agreement by category. 

 

So the simple example above would give me 50% agreement for code 1XY45 and 1 survey with less than 70% agreement for the category MED

 

I seem to be able to calcualte the % Agreement but cant seem to be able to calculate and filter on the % agree measure.

 

Maybe I need to sumamrise the data?

 

Thanks so much, 

Keelin

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

Give this a shot

 

% Agree <70 =
COUNTROWS (
    FILTER ( ALLSELECTED ( Sheet1[course_code] ), [11 % Agree Col] < .7 )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

    In that case.....Try Replacing allselected with VALUES

     

    % Agree <70 =
COUNTROWS ( FILTER ( VALUES ( Sheet1[course_code] ), [11 % Agree Col] < .7 ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Zubair_Muhammadis a good solution. The alternate is there:

     

    % Agree <70 = CALCULATE(
	COUNTROWS(Sheet1),
	ALLSELECTED(Sheet1)
	[11 % Agree Col] < 0.7
)

    I am a big proponant of avoiding mixing Filter and Calculate together unless absolutely necessary.  99% of the time adding filter to calculate is redundant.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    % Agree <70 =
COUNTROWS (
    FILTER ( ALLSELECTED ( Sheet1[course_code] ), [11 % Agree Col] < .7 )
)

    Zubair_Muhammad 

     

    Hi  Zubair_Mohamad, this is bringing back the overall result of the number of course codes <.7.  It breaks when I try to use the measure to show results by category in a table. I cant seem to work in the categories, for example the simple MED example above. Do you know how I work other filters into this?  Seems like we are part of the way there. 

    Thanks

    Keelin

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Zubair_Muhammad you're a rock star!

     

    Thanks so much for this, it worked. I'll be able to deliver the report on time now. Thanks again