8 Replies Latest reply on May 14, 2018 5:52 AM by Okechukwu Ossai

    wrong percent in some cases

    george.gousios

      Hi, another wrong percent problem that I can't figure it!

       

      So, I can see that my total number of data rows is 3745 (Total rows sheet, view data) (few of them are null so I don't know if tableau is counting as rows but it is a negligible number).

       

      In the South sheet , I am using filters to find out for 0-4 beaufort wind speed and South wind direction.So, I can see that I have 298 rows , so a percent of 298/3745 =  7.9572%

       

      In the East_0_4 sheet , I am using filters to find out for 0-4 beaufort wind speed and every East wind direction (NE,E,SE).So, I can see that I have 770 rows , so a percent of 770/3745 =  20.5607%

       

      In the East sheet , I am using filters to find out for 4-5 beaufort wind speed and  every East wind direction (NE,E,SE). So, I can see that I have 109 rows , so a percent of 109/3745 = 2.91%

       

      Now, in the wind radar sheet , I want to confirm those percents.

       

      I am calculating the percent with 2 ways.

       

      1) Percentage:

      (COUNT([Wind Speed]) / TOTAL(COUNT([Wind Speed]))) * 100

       

      which gives the right results for 0-4 beaufort

       

      0-4 bf,  South : 7.9893%  (very close to 7.9572%)

      0-4 bf,  Easts : 9.8123 + 7.1850 +3.6461 = 20.6434% (very close to 20.5607%)

       

      but not for 4-5 beaufort!

       

      So, If I check the Wind radar sheet -> view data and check for Wind speed color 4-6 , I can see that for all east directions , I have : 0.5630 + 0.9651 = 1.5281%   instead fo 2.91%

       

      (the same goes for other combinations)

       

      2 ) I just added the SUM(Number of records)

      which gives wrong results whatever I try.

        • 1. Re: wrong percent in some cases
          Okechukwu Ossai

          Hi George,

           

          Modify your percentage formula as shown below. I have used count of date, assuming that the date field will not have any null values.

           

          Hope this helps.

          Ossai

           

          • 2. Re: wrong percent in some cases
            george.gousios

            Hi Okechukwu and thanks for the help.

             

            I can see, that you count the different timestamps, so you count the rows of data and you show the percent.

            So, by putting filters for speed and direction, you show the percentage (for example 2.91% as you have).

             

            But, if you check on the wind radar sheet , which uses no filters , it does not show the correct percentage (2,91%) but 0.5607 + 0.9613 = 1.522%.

            I want to be able to show the percentage in the wind radar plot without using any filters.

             

            Thanks!

            • 3. Re: wrong percent in some cases
              Okechukwu Ossai

              Hi George,

               

              I can see where the issue is. The correct figure is 1.522% and not 2.91% You were getting 2.91% because you were filtering with the wrong field. If you go to Sheet 6, right click on 2.91% cell and select View Data, in the box that opens, go to the bottom and click 'Full Data'. This will show you all the data included in the 109 rows. It includes some '0 - 4' records in the count. This is because you are filtering with  [Wind speed bf] instead of [Wind speed color]. Setting Wind speed bf filter from 4 to 6 is not the same as Wind speed color group '4 - 6'. Check your Wind speed bf calculation again, category 4 includes Wind Speeds between 10 and 15. These speeds belong to the Wind speed color '0 - 4' category.

               

               

               

               

               

              Using the correct filter in Sheet 6 will give you 1.522%  (57/3745). The view below shows the correct number of records per wind speed color and wind direction group.

               

               

               

              Hope this helps.

              Ossai

              1 of 1 people found this helpful
              • 4. Re: wrong percent in some cases
                george.gousios

                Hi Okechukwu ,

                 

                 

                Good catch! I totally missed that!

                Thanks s lot!I appreciate.

                 

                • 5. Re: wrong percent in some cases
                  Okechukwu Ossai

                  You're welcome George. Glad it helped.

                  • 6. Re: wrong percent in some cases
                    george.gousios

                    Hmm..

                     

                    I noticed that if I put a date time filter, then the percent is "not right", because as I can see we are taking into consideration all records (we are dividing by SUM({FIXED : COUNT([Date Time])})).

                     

                    Is there a way , to avoid this?

                     

                    Thanks again!

                    • 7. Re: wrong percent in some cases
                      Okechukwu Ossai

                      Hi George,

                       

                      The observed behaviour is typical of LODs. There are 2 possible options.

                       

                      Option 1: Add Date Time filter to context.

                      Option 2: Add the fields you plan to filter on to the LOD.

                       

                      I'm not exactly sure how the calculations behave when you filter by date. So, I suggest you go with option 1 and add the filter to context and see if that works.

                       

                      Hope this helps.

                      Ossai

                      1 of 1 people found this helpful
                      • 8. Re: wrong percent in some cases
                        george.gousios

                        Thanks again!

                         

                        I didn't know about the context filtering option.This works great.

                         

                        As for option 2 , I was trying {FIXED [Date Time]: COUNT([Date Time])} which is different from the one you suggest and gives different results.I would expect to work though since we are counting the rows by Date time field..

                         

                        Anyway, using the second option you suggested ruins the graph, since now I have to use a table calculation and whatever I try doesn't work.

                         

                        Thank you!

                        • 9. Re: wrong percent in some cases
                          Okechukwu Ossai

                          You're welcome. Option 1 should work better for your use case.