6 Replies Latest reply on Jan 4, 2017 3:41 AM by Damith Gunasekara

    Custom tooltips with percentage in a doughnut chart

    Damith Gunasekara

      Hi All,

       

      Is it possible to have custom tooltips with measure value and percentage to be displayed in a doughnut chart?. i'm trying to dynamically display custom tool tips with measure value and percentage in a doughnut chart.it is working fine with measure value but when trying to add percentage it is not showing the correct percentage value.

       

      I have attached a sample workbook

       

      Thanks,

      Damith

        • 1. Re: Custom tooltips with percentage in a doughnut chart
          Simon Runc

          hi Damith,

           

          So yes this can be a tricky problem...

           

          So what I've done is create 2 calculated fields to work out each of the %age New and %age Returning. I've set these up in such a way, that it only shows the mark label for the selected segment (New or Returning), and equates to NULL when the other is selected...so don't get plotted.

           

          so first I need to create a %age New

          [New %age - Label]

          if [New Profiles tmp] != ""

          then

          {FIXED [Select Device Profile Type]: [New ]/(zn([Returning ])+zn([New ]))}

          end

           

          I've used an LoD here, so I can run the  [New Profiles tmp] != "" at row level (and so not need this in the VizLoD). I've also created this LoD against [Select Device Profile Type], as you have this in your filter, and so you (probably) want a different value for each of these (if you have other dimensions/slices, you'll need to add these to the left part of the LoD calculation

           

          I then do something similar for returning

          [Returning %age - Label]

          if [Returned Profiles tmp] != ""

          then

          {FIXED [Select Device Profile Type]: [Returning ]/(zn([Returning ])+zn([New ]))}

          end

           

          I then bring these both onto the Label Shelf and put then side-by-side (remember only one ever shows at a time)

           

           

          now when you select a segment, then the %age also shows (along with the count) for that segment. You could do the same thing to get them on tooltips too.

           

          Hope that helps and makes sense, but let me know if not.

          • 2. Re: Custom tooltips with percentage in a doughnut chart
            Damith Gunasekara

            Hi Simon,

             

            Thanks a lot! this is exactly what i was looking for.I was struggling with it for sometime

            Great work!

             

            Thanks,

            Damith

            • 3. Re: Custom tooltips with percentage in a doughnut chart
              Simon Runc

              Excellent, glad it helped

               

              ...it was quite timely for me too. I had exactly the same problem with a model at work the following day (so was glad I'd answered this the day before!!)

              • 4. Re: Custom tooltips with percentage in a doughnut chart
                Damith Gunasekara

                Hi Simon,

                 

                I need your help to resolve an issue i faced when showing percentages for doughnut charts when using multiple values for a quick filter.I have updated the sample workbook and added a new filter 'client_code' and updated 'New% -Label' and 'Returning %age - Label' calculations to include client code to the Fixed LOD in addition to '[Select Device Profile Type]'

                 

                Eg:- {FIXED [Select Device Profile Type],[client_code]: [New ]/(zn([Returning ])+zn([New ]))}

                 

                With this expression the percentage values are showing correct when selecting a single client code,but when selecting multiple client codes the percentage values are incorrect

                 

                Would you be able to check and help resolve the issue?

                I have attached updated workbook for reference

                 

                Thanks,

                Damith

                • 5. Re: Custom tooltips with percentage in a doughnut chart
                  Simon Runc

                  hi Damith,

                   

                  So I've had a quick look at this, and it's what is going on here is actually a little bit complicated. I'll do my best to explain (and also think I may have a far simpler solution!!)

                   

                  So the problem with how we currently have it set up is that as a LoD the results are returned at the FIXED level ([Select Device Profile Type] and [Client_Code]), and then we need to aggregate the results. You have chosen MIN, which works great when we only have one Device and one Client....as you'll have seen using SUM doesn't work due to the way we are aggregating things (it'll just add up the %ages). This means that when you add another client_code, it just takes the MIN.

                   

                  So we need to aggregate each side of the percentage calculation...something like this

                   

                  SUM({FIXED  [Select Device Profile Type], [client_code]: [Returning ]})

                  /

                  SUM({FIXED  [Select Device Profile Type], [client_code]:(zn([Returning ])+zn([New ]))})

                   

                  However, we now have an aggregated calculation...and we need to do the row level test (as everyone is all too aware we can't mix aggregates and non-aggregates)...However, we can't just do this

                   

                  if  ATTR([Returned Profiles tmp]) != "" THEN

                  SUM({FIXED  [Select Device Profile Type], [client_code]: [Returning ]})

                  /

                  SUM({FIXED  [Select Device Profile Type], [client_code]:(zn([Returning ])+zn([New ]))})

                  END

                   

                  as we don't have [Returned Profiles tmp] in the VizLoD, as all the values are different ATTR, MIN, MAX won't do what we want and we can't add [Returned Profiles tmp] to the VizLoD, else we'll get a slide per customer!

                   

                  So I created a Dimension, which I could add to the VizLoD and use that instead....

                  [New/Returned Dim]

                  IF [New Profiles tmp] != '' THEN 'New' ELSE 'returned' END

                   

                  and then I can adapt the formula above to be

                  [New %age - Label - SR]

                  if ATTR([New/Returned Dim])= 'New' THEN

                  SUM({FIXED  [Select Device Profile Type], [client_code]: [New ]})

                  /

                  SUM({FIXED  [Select Device Profile Type], [client_code]:(zn([Returning ])+zn([New ]))})

                  END

                   

                  and similarly for returns.

                   

                  So far so good...although I couldn't get the figures to quite add up, and on an investigation, I found the issue. If I select, say "orgc1" and "orgc2" and "Desktop" we get the problem. Orgc2 has no "New", and as the [returned] results are calculated at [Select Device Profile Type] and [Client_Code] level, the 15 returns (from the table below) aren't added to the total Profile calculation (that we use to get the average)

                   

                   

                  So to try and get round this problem, I changed the [New %age - Label - SR] formula to only be returned at Device level

                  [New %age - Label - SR]

                  if ATTR([New/Returned Dim])= 'New' THEN

                  SUM({FIXED  [Select Device Profile Type]: [New ]})

                  /

                  SUM({FIXED  [Select Device Profile Type]:(zn([Returning ])+zn([New ]))})

                  END

                   

                  An another however!...FIXED LoDs are calculated before any regular filters are applied (this is to do with Tableau's order of operations)...so the [Client_Code] filter has no affect on the result. So to get around this I made that filter "in context", which bumps it up the calculation chain.

                   

                   

                  So now it works (this is on the "LoD Version - SR" tab)...but it did get me thinking that this seems crazy complicated, for such a problem...and started thinking about the dimension we created earlier...

                   

                  [New/Returned Dim]

                  IF [New Profiles tmp] != '' THEN 'New' ELSE 'returned' END

                   

                  So LoD free I did this...

                   

                  I created a single column with both Returned and New Profile IDs in

                  [All Profiles]

                  IF [New Profiles tmp] != '' THEN [New Profiles tmp] ELSE [Returned Profiles tmp] END

                   

                  I then just brought this field in as a COUNTD...and used the [New/Returned Dim] dim above to colour the pie. I also had to filter out rows where [All Profiles] was blank (rows that are empty for both New and Return profiles). I was then able to use the "built-in" table calculation on "Percentage of Total" to get the %ages. As it's LoD free, all filtering now works as expected without any context filters, problems with client_codes that do not have new or returns...etc. I've put this on the "Do We need LoDs?" tab.

                   

                  It might be that this is just the example you posted, and in your real world situation you do need to use the LoDs (anseparatete measures), but thought I'd share this anyway (why make life harder!!)

                   

                  Hopefully that makes (some) sense... but let me know if not.

                  • 6. Re: Custom tooltips with percentage in a doughnut chart
                    Damith Gunasekara

                    Hi Simon,

                     

                    This is Perfect!  Thanks a lot for taking time to check the issue and providing both solutions.I didn't know it's possible to achieve the same result without using LOD's. Smart work!

                     

                    Thanks,

                    Damith