2 Replies Latest reply on Mar 16, 2016 10:57 AM by pooja.gandhi

    Dashboard Filters using Categorical data that are not part of Worksheet definitions

    Richard Gordon

      The purpose of this discussion is to determine how to

      1. Create a dashboard filter with data variables that are not part of Worksheet definitions AND
      2. Create a hovering action only using the legend (I can use the dashboard graphics, but would prefer a more restricted approach).
      3. Fix the Value_Diff calculation to include the Grand Total Value differences

       

       

      Use Case

      The dashboard target looks to illustrate the following:

      1. Sample profile distributions across segments
      2. Important distributional profile differences, if present.
      3. Profile value differences that may or may not be affected by the Segment
      4. Provide a common view across exogenous filter variables to drill downthe results.

       

      Notes

      The attached tableau file has the supporting worksheets and target dashboard I am looking to improve.

      The Value_Diff calculation is best seen on worksheet Value Dist&Diff Chk, which includes a summary table of counts, average values, and value column differences.

      My data model has 26 worksheets with different combinations aggregated into 5 dashboards, each of which needs to be filtered on data not part of their worksheet definitions.

      I have this data model up and running on an excel Power Pivot spreadsheet with slicers, the question is, "how to accomplish in Tableau?", which can socialize the dashboards more easily.

       

      Background

      There are 2 background discussions that has brought me this far and a big shout out to Yurij Fal and Pooja Ghandi for their help.  The previous discussions can be reviewed, if necessary, at the following discussion labels and links.

       

      How do I create a Distribution Differences by Segment chart?

      https://community.tableau.com/message/463102?et=watches.email.thread#463102

       

      How do I calculate Value Difference over a X by Y contingency table

      https://community.tableau.com/message/464981?et=watches.email.thread#464981

       

      To be sure, the Value Difference approach provided worked, but with limitations.  The suggested approach Value_Diff = {fixed [Profile], [Segment] : avg([Value]) } - { fixed [Segment]: avg([Value]) } provided the correct cell differences, but did not compute the Row Grand Total correctly and did not adjust to exogenous filters on the data.  The adjusted approach Value_Diff = AVG([Value]) - sum({EXCLUDE [Profile]: AVG([Value])}) does get the Row Grand Total zero'd out correctly and will adjust to exogenous filters.  However, in the Value Dist&Diff Chk, you can see that the calculation does not adjust for the Column Grand Total.

       

      Thanking you in advance,

      rg