1 Reply Latest reply on Aug 24, 2016 6:40 PM by Tom W

    Double-Counting with different Measure Names?

    Nathan Jones

           I've got some data that looks like the below table.

       

           What I'm looking for is a method to tally the number of questions in which 'diversity' appears in ANY index, for example.

       

      QuestionIndex 1Index 2Index 3
      1DiversityLeadershipFacility
      2LeadershipDiversityFacility
      3Leadership
      4Diversity
      5Facility
      6Facility
      7DiversityFacility
      8OverallFacilityDiversity

       

      So, COUNT(Diversity) (well, the calculation that's analogous to that) should be equal to 5.

       

      The below logic seems like it's almost there, except it only counts each row once.

       

      if [Index 1] = "Facility"

      OR [Index 2] = "Facility"

      OR [Index 3] = "Facility"

      THEN "Facility"

       

      elseif [Index 1] = "Diversity"

      OR [Index 2] = "Diversity"

      OR [Index 3] = "Diversity"

      THEN "Diversity"

       

      ELSEIF  [Index 1] = "Leadership"

      OR [Index 2] = "Leadership"

      OR [Index 3] = "Leadership"

      THEN "Leadership"

       

      ELSEIF  [Index 1] = "Overall"

      OR [Index 2] = "Overall"

      OR [Index 3] = "Overall"

      THEN "Overall"

       

      END

       

      But only counts each line once.  Any idea of how to get the double-tally going so that they can appear in the same graph?  I could make 3 separate graphs but it complicates things for filters and connectivity.

       

      See attached workbook. 

        • 1. Re: Double-Counting with different Measure Names?
          Tom W

          The problem is that each row can only evaluate to true once.

          For what you're trying to do, you really need to look into pivoting your data so you have one row per Index.

           

          You could create a calculated field per index i.e.

          COUNT(IF [Index 1]="Diversity" then [Index 1] end)

          +

          COUNT(IF [Index 2]="Diversity" then [Index 1] end)

          +

          COUNT(IF [Index 3]="Diversity" then [Index 1] end)

           

          then include this in your report using the measure names and measure values pills, but I'm not sure if that will work in your real scenario.