5 Replies Latest reply on Aug 27, 2018 8:52 AM by Andrew Bickert

    Shapes Based On Measure Values

    Dana Chaffin

      I'm replicating a Tableau Dashboard to point directly to a SharePoint List. The original pointed to an Excel sheet (which pulled the information from the SharePoint List) and used custom SQL.


      For the most part, I've been successful. The one area I'm having trouble is replicating what they did using UNION in SQL (below)


      8-24-2018 1-54-43 PM.png


      The Status Types of Overall, Scope, Schedule, etc are different columns and they created a 5 union SQL where they were able to "pivot" each of the Status Types and the scoring into 2 columns (Status Type and Status) where they were able to create KPI shapes based on the Status


      My struggle is that SharePoint Lists can't be unioned, joined, have custom SQL, etc.


      I've gotten this far (below) where I converted each of the Status Type scores to a value and created a Measure Value and show the Measure Names (to get the list). I just now need to get those circles into KPI Shapes based on the values (1 = Green Circle, 2 = Yellow Triangle, etc)


      8-24-2018 2-05-36 PM.png


      How can I get the Measure Values to register as Shapes (or make them discrete)


      Unfortunately, I can't posted the workbook.



        • 1. Re: Shapes Based On Measure Values
          Andrew Bickert

          Hi Dana,


          You should be able to do this with a calculation. Assuming that your Status Type is your Score Value you can use:



          IF [Status Type]=1 then 'Green Circle'

          ELSEIF [Status Type]=2 then 'Yellow Triangle'

          ELSEIF [Status Type]=3 then 'Red Diamond'

          ELSEIF [Status Type]=4 then 'Grey Circle'




          Then you can drag this to your Shape Area and choose the associated Shape and Color.


          See attached for example.



          • 2. Re: Shapes Based On Measure Values
            Dana Chaffin

            Thank you for the quick response.


            My example may have caused some confusion. I don't have a column called Status Type. The Status Type column was created through the UNION SQL (which isn't an option with SharePoint List - as far as my research can tell)


            I have 5 columns: Overall, Scope, Schedule, Resources, Budget. I created 5 calculated fields called Overall Value, Scope Value, etc (screenshots below) to capture the scores from each of those columns and created a Measure Value and break it out using Measure Names


            8-24-2018 2-57-38 PM.png

            8-24-2018 2-57-46 PM.png


            I have 4 values (1 to 4). As an example: Overall could be 1 and Scope could be 2 for the same project on the same date


            How am I able to show a GREEN circle in the Overall and a YELLOW triangle for Scope?

            • 3. Re: Shapes Based On Measure Values
              Naveen B

              Hi Dana,


              Drag Measure Names field to Shapes, then change the shapes accordingly based on your requirement


              Hope this helps


              Plz mark it helpful and correct to close the thread




              • 4. Re: Shapes Based On Measure Values
                Dana Chaffin

                Thanks for the help Naveen.


                I tried your suggestion. Below are the screenshots.


                Basically, placing the Measure Names on Shape applies the same shape on the entire measure and doesn't change based on the value in the measure


                Does that make sense? I'm needing the Shapes to be on the value AND have it recognized at the Measure Name level


                8-27-2018 8-23-02 AM.png


                8-27-2018 8-23-41 AM.png

                • 5. Re: Shapes Based On Measure Values
                  Andrew Bickert

                  Hi Dana,


                  It makes it a bit trickier if you are using multiple measure types as you cannot drag Measure Values onto Shapes. You can mimic this dragging each Measure up to your Rows and then having a calculation for each measure to decide on shape/value. The most annoying thing about doing it this way is your headers are sideways. To get around this you would need to use the annotation/text to show your headers unless you don't mind them being vertical.


                  Sideways headers and Sheet Layout


                  With headers removed and annotations