1 Reply Latest reply on Apr 20, 2018 8:23 PM by swaroop.gantela

    Compare calculated % of total to given % of total on same sheet, data blend

    Jessica Singer

      I'm new, and I'm struggling. Apologies in advance, this is probably an easy one.

       

      I have one dataset whose rows are individual program participants and their associated demographic info. My other dataset is aggregated at the council district level, and given in percentages.

       

      I want to compare the ethnicity percent of total per council district of the program dataset to the ethnicity percent of total of the council district dataset on the same sheet, preferably side by side. Since the datasets are at two different levels of aggregation, I can't use a quick table calculation for one half. I'm struggling with how to create a calculated field that will allow me to compare these side by side.

       

      Thank you!

        • 1. Re: Compare calculated % of total to given % of total on same sheet, data blend
          swaroop.gantela

          Jessica,

           

          Welcome to the Forum.

           

          There are very likely easier ways to accomplish it as compared to the proposed below,

          but maybe this can give an idea.

           

          This may not work for your true data sources, because my first step is to union them together.

          This allowed me to union the two together, so the shared columns of District and Ethnicity could be used.

           

          This creates a new Dimension called [Table name], which I employed in a calculated field to get the Program Percentage:

          SUM(IF CONTAINS([Table Name],"ProgramTable") THEN [Number of Records] END) / TOTAL(SUM(IF CONTAINS([Table Name],"ProgramTable") THEN [Number of Records] END))

          (This had a compute using of [District] and [Ethnicity], restarting every [District].)

           

          I then used a dual axis to put the Council Percentages on axis and the above calculation on the other.

          I was able to get them side-by-side by putting [District] on Columns followed by [Table Name]

           

          To restrict the view to just the districts that were in the Program, I used a filter of:

          { FIXED [District]:COUNTD([Table Name])}>1

           

          Lastly for the Borough Filter, the Program Table didn't have Borough,

          so I fixed the Borough to the District:

          { FIXED [District]:MAX([Borough])}

           

          This is definitely a more round-about way to get to your graph.

          Likely there are underlying consequences of pursuing this route.

          If this doesn't work for your set up, will explore other methods.

           

          267730comp.png