3 Replies Latest reply on Jul 7, 2016 10:18 AM by Aiswarya Sundaram

    maximum axis across worksheets using different dimensions and measures

    Sarah Delaney

      I think I need to create a calculated field to add an invisible reference line to create a maximum axis across worksheets, but unlike Maximum Scale(Axis) across all work sheets , my situation is using different dimensions in each worksheet.  I also plan on using a parameter allow the user to change the measure,  which adds another change from the solution I found. I'm not sure how to create a max with my situation and even if I get that working, dropping them into the dashboard with the same bar with is an issue.


      I'm hoping someone has pointers on creating the max for my situation or another way to look at my problem because my original idea is awkward!


      Business Problem:

      We need to report by ethnicity and our mandate is to present our measures by all ethnicities a student  reports. The original data has a single, 7 bit field with each position representing an ethnicity. We have created additional indicator flags to separate the one field into 7 separate indicator fields, one for for each ethnicity. You can't just create a grouping field for original 7 bit field because a single student will need to be aggregated under multiple ethnicities. For example, a student who reports both Asian and White is coded as 1000001 and would have their headcount included in the Asian counts as well as the White counts.   See the sample data.



      My original idea was to create a worksheet for each ethnicity indicator field and place them next to each other in a dashboard so they look like a single chart. Even if I can get the axis the same, I'm having issues dropping them into the dashboard and getting the bar widths to look the same.







      Sample Data:

      Maximum Scale(Axis) across all work sheets



        • 1. Re: maximum axis across worksheets using different dimensions and measures
          Aiswarya Sundaram



          In order to get the charts to be of the same size, you would have to spend a lot of time formatting.

          Place them in a horizontal container and make sure all the charts are of the same width or just width of dashboard/number of sheets and some extra space for the axis.

          Also the bars to be same size, you could create a variable with value 1 and put it on size then manually adjust using the controlling as now you have more control than before.


          What I would also suggest is look at data blending. The other data source would be a column for EthnId and another for the Name. Keep this as the primary and link it with your original source on EthnId.


          If you are using Excel then add another sheet on the above mentioned format and do a left join between these two sheets on EthnId


          Its too late in the night for me here to actually do it!

          But i'm certain this will help you.

          • 2. Re: maximum axis across worksheets using different dimensions and measures
            Sarah Delaney

            Thanks so much. I have tried most of your suggestions. Here's the status


            Same size charts issue: resolved

            I had originally put them in a vertical container so I tried a horizontal. The first one is wide, the next one they are even or so, then each additional one gets thinner. I started over with a new horizontal container, but formatted it with a background color. This time is was sure I was putting them in the same container and they automatically resized. Evidently, I'm just not seeing the visual clue for the container I want and I was sticking them in the wrong place!


            Column width issue: implementation error???

            I created a new calculated field and ragged it into size and set the aggregation to minimum, but that didn't change the bar widths in the dashboard. If you click on size, the slider is 3/4 to the right, no matter what you change the value of the variable to. Did I miss a step/


            Data blending: need more info

            I'm unclear what field you are reference with Name. Do you mean the 7 ethnicity flag fields? StudentID is the primary key of the data set, which is currently 200k rows in Access . (My goal is to get it into SQL server after we work out our proof of concept.) How would the blend work? All I can think of is a join on StudentID.


            Thanks again!

            • 3. Re: maximum axis across worksheets using different dimensions and measures
              Aiswarya Sundaram

              Here you go!

              I created a dummy dataset based on what i think it would be the data structure from the image on top and came up with a dummy dashboard.


              Column width:

              After creating the variable and dropping it onto the size mark, click on the legend and edit your size range.


              Data blending:

              I created a sheet with the Ethnicity ID and the Name you give it. For example:

              100001 - Pacific Islander

              100001 - Asian

              100010 - Asian


              So it's like a lookup table for the Ethnicity ID and you enter all combinations in it.

              And then blend it with the Ethnicity ID in your original data set, keeping the newly created 'data source' as primary.


              The main drawback of this method would be repetition of student records.

              If you are looking at just head count its fine but if you are going to display marks then make sure you take an AVG aggregation instead just to be sure.


              I feel its better you avoid the join and keep it as blend because then you can go about with treating your other measures normally without having to worry about AVG or SUM in other sheets where this grouping isn't required.


              Hope its clearer now and was of some help.