3 Replies Latest reply on Jul 13, 2016 12:39 PM by Vincent Baumel

    Dashboard Filter from a dimension to a measure (w/o parameters)?

    Nathan Jones

      Hi all,


      I'm creating a demographics dashboard.  the first view is a simple Gender breakdown, by count:



      The second view is a geographical map:



      The functionality I'd like is to click on Female in the first dash, and update the geographical map to display a calculated field Female Percentage:


      COUNT(IF [Gender]='Female' THEN [Gender] ELSE NULL END) / Count([Gender])


      Likewise, when I click on Male, the geographic map should update to display Male Percentage:


      COUNT(IF [Gender]='Male' THEN [Gender] ELSE NULL END) / Count([Gender])


      I've tried to do this via another calculated field, Percentage Switch - Gender:


      if ATTR([Gender]) = 'Male' then [Male Percentage]

      elseif ATTR([Gender]) = 'Female' then [Female Percentage] END


      But, as you might have guessed, this doesn't work, because it filters down to only male or female values, and shows all percentages as 100%.  Is there any way to accomplish this functionality, preferably without parameters?  Sample workbook attached.

        • 1. Re: Dashboard Filter from a dimension to a measure (w/o parameters)?
          Vincent Baumel

          What you're looking for is a specific use of Level of Detail calculation. I've gotta give some credit to #4 on http://www.tableau.com/about/blog/LOD-expressions which helped me to understand exactly what's going on. Essentially, we're looking at aggregations occurring at two levels - a count of records specific to what you've chosen, and a count of records overall. To visualize this, I created a sheet with a filter on Gender, that only let me choose either Male or Female. I put Location on the rows shelf, and SUM(Number of Records) on the columns shelf. This gave me a count of records for each location, and by manipulating the filter I was able to dynamically change the counts. Simple enough, so I created a second column pill and typed in the same thing, only in brackets: {SUM(Number of Records)}    What this did was expand the aggregation out to include all records, not just those that are being limited by the filter. All of these bars read the same, which makes sense! Now for the only tricky part: Create a calculated field called "% of Total" that reads like this:


          SUM(Number of Records) / MIN( {SUM([Number of Records]) } )


          This will take those filtered amounts and divide them by the total record counts. Right click this in the measures pane, click Default Properties -> Number Format -> Percentage. Now we can go back to the Map worksheet and adjust it so that the size is based on our "% of total" calculation. Turn on the labels, and we're good to go! Your dashboard action will take care of the rest!



          • 2. Re: Dashboard Filter from a dimension to a measure (w/o parameters)?
            Nathan Jones

            Vince -- this is almost there!  Thanks for the detailed post.  I don't even have 10.0, so I couldn't open the workbook, but it was easy to follow along.  Thanks for that.


            One thing: Your workbook gives percent of total.  What I'm looking for is percent of females (or percent of males) at each location.  So, theoretically, the map would look like this, with the percentage of females compared to males at that particular location




            Hence this calculation:


            COUNT(IF [Gender]='Female' THEN [Gender] ELSE NULL END) / Count([Gender)]) ...what my client wants to do is select "male" and see the percentages of males at each location comparable to females, and later, for example, select "Asian" and see the percentage of "Asian" employees at that location as a fraction of the total numbers of employees at that location.


            Does that make sense or am I muddying the waters further? 

            • 3. Re: Dashboard Filter from a dimension to a measure (w/o parameters)?
              Vincent Baumel

              Makes perfect sense. Here's what we're looking at for a solution: 5 calculated fields is all we're gonna need. I've annotated each calculation to explain what they all do.



              Let me know if this is what you're looking for! I put together a combined dashboard as well, although you're action filter still works to switch from the Breakdown to the Map.




              2 of 2 people found this helpful