1 Reply Latest reply on Apr 10, 2018 9:48 AM by Eric Hammond

    Help with Side by Side Comparison of Individual vs Group in Text Table

    Dan Findley

      I'd love to link to data, but it's all healthcare data and I can't share any of it, so I'm hoping this description helps clarify things sufficiently.

       

      In this text table, I have three calculations. I want to compare each calculation against itself. I've tried using LOD calcs, but with the formatting I want to use for the table, it isn't working.

      In this data set, there's a hierarchy: Parent Group > Functional Group > Type > Center. For each calculation, let's call A the filtered column, and B the compare column, for each of the three calculations.

       

      I want to have values for A change every time I pick from any tier in the hierarchy. I want column B to change when I pick a Parent Group or a Functional Group, but stay locked when filters change the Type or the Center.

       

      One further complication is that the calculation values are presented at a "driver" level, which does not fit into this hierarchy, although it somewhat does. Let's say a driver name is "Band-Aids Used". I want the value for this driver in Column B to reflect the overall average at the Functional Group Level, whereas I want this value to change in Column A at the Type and Center levels.

      I have tried using a FIXED LOD at the Functional Group and Driver levels, and this hasn't given me the correct values.

      I have tried using an EXCLUDE LOD at the Type and Center levels, and while this works, the view is messy. I have to include too many columns in my view for users to make sense of - the whole point is to allow users to drill and easily compare an individual selection vs its peers. When I filter all the way down to the Center tier, my columns in A and B show the same values, because EXCLUDE isn't flexible enough for what I want to do.

       

      I know it's more helpful to link a workbook, but I'm unable to do that. Does anyone know how to accomplish this?

       

      Thank you!

        • 1. Re: Help with Side by Side Comparison of Individual vs Group in Text Table
          Eric Hammond

          Hi Dan,

           

          Consider leveraging the filtering functionality of dashboard actions, set so that clicking on a row at any level filters all child levels.  To do this, break up each level of the hierarchy into a separate worksheet and then add them all to a dashboard; then add filter actions (under the dashboard menu).  Below is an example with the three measures (butchered from Superstore) shown for each level (though I only did three levels).  If the measures take up too much space, then you can remove them from the hierarchy worksheets and put them on separate worksheets (one for A, one for B, as described in your post) that get filtered based on hierarchy selections.

           

          For consistency (and ease of development), create/format/refine the worksheet for Parent Group first, then for the other levels just duplicate the finished worksheet and swap out the dimension that is on rows.