1 Reply Latest reply on Jan 28, 2016 10:14 AM by Justin Wagner

    IF Statement Question

    Justin Wagner

      Scenario - I'm trying to create three individual calculations (Books Count, TV Count, Music Count) using an IF statement based on the data below.

         

      DivisionDepartmentSurveyTotal
      101Books2978
      101Tv4784
      101Music1425
      102Books4580
      102Tv3142
      102Music927

       

       

      Example calculation for Book Count:

       

      IF ATTR([Department]) = "Books" THEN SUM([Survey])/SUM([Total])

      ELSE 0

      END

       

      I end up with 0 as my result unless [Department] is in the canvas.

       

       

      Is there any way to populate the numbers without having to bring in department? I can also attach the workbook for testing if needed.

       

      Thank you.

        • 1. Re: IF Statement Question
          Justin Wagner

          Version 9.1

           

          I would just drop the IF statement and use the quick filters but I wanted to have three individual columns (IF Statement) instead of one measure and a filter selection. They want the data displayed a certain way in the dashboard. And I think I might have figured it out by wrapping an AVG() function around the whole thing and dropping the aggregation in the calculation.

           

          Instead of

          IF ATTR([Department]) = "Books" THEN SUM([Survey])/SUM([Total])

          ELSE 0

          END

           

          I just did

          AVG(

          IF ATTR([Department]) = "Books" THEN [Survey]/[Total]

          ELSE 0

          END)