5 Replies Latest reply on Oct 7, 2017 6:26 AM by Phil Domenico

    Table Calculations across measure values

    Brandon Eller

      I have a dataset that is uniquely structured and we are trying to create visualizations without altering the data. We are creating stacked bar charts colored by Measure Names with the Measure Values as a label on the bar. Now the task is to compute a percent of total using the two labels. Is there some way to create a measure with IF [Measure Name] == "X" THEN ____? Is there another way to approach this?

        • 1. Re: Table Calculations across measure values
          pooja.gandhi

          Hi Brandon,

           

          Using measure names/measure values and asking for what you need is difficult. Instead I would use the 2 measures separately on rows so that it creates a separate axis for each measure. Formatting the axis individually makes it much easier to include percent of total etc. If you use measure values and create a separate calc to label the percent of total, the label will apply to both sections of the bar and hence the suggestion of using 2 different measures and then using dual axis to combine the 2 charts. You can create 2 calcs like:

           

          HH Not Shopping Calc: sum([HH Not Shopping]) / (sum([HH Not Shopping]) + sum([Retailer Shoppers]))

          Retailer Shoppers Calc: sum([Retailer Shoppers]) / (sum([HH Not Shopping]) + sum([Retailer Shoppers]))

           

          Then place these calcs on their corresponding axes along with the original measures, so that both percent and actual numbers are labelled. You will want to right click on the right axis and synchronize axis and right click on the left axis to bring the marks forward. Then use the size slider to size the bars accordingly so the chart gives an appearance of a stacked bar. I am not sure how you are getting the percentages that you get, because I have different values. Additionally click on the drop down of the second pill on rows and uncheck 'show header' so only 1 axis is visible. Hope this helps:

           

          • 2. Re: Table Calculations across measure values
            Brandon Eller

            Very helpful Pooja Gandhi. Thank you. Do you have ideas of how I would approach this if there were 3 measures and thus 3 colors on my stacked bar?

            • 3. Re: Table Calculations across measure values
              Dan Murray

              This can be done easily by using the data interpreter to pivot the columns, then build a stacked bar chart using a table calculation to create a % of Total table down, then add the value and table calculation on lables. I've created a video to show you how.

               

              Dan

              1 of 1 people found this helpful
              • 4. Re: Table Calculations across measure values
                Dan Murray

                1 of 1 people found this helpful
                • 5. Re: Table Calculations across measure values
                  Phil Domenico

                  Dan, nice video quality presentation.