2 Replies Latest reply on Nov 8, 2018 5:59 AM by Jim Dehner

    Share of total for month depending on filter

    Andrew Brew

      Hey guys,

       

      are you able to help me with the following issue.

      I'd like to have stacked bar chart by months of the year with two measures:

      1. % of total sales per month for specific city/cities (specified in the filter)

      2. % of total of the other cities for this month

       

      For example if we filter by city: Albuquerque then we should see similar to the stacked bar chart below.

      where 'orange' bar is % of total sales for this month (Albuquerque vs Rest of Cities for given month)

      [ let's say 2% for specific month]

      and

      'blue' bar is % of total sales for this month for the rest of the cities

      [ then it would be 98% for specific month]

       

      This should be dependent on the city/cities specified in the filter. If we choose multiple cities - then orange bar should adjust for multiple cities sales value in relation to Total of other cities.

       

      I am not sure if LOD should be used here, because Total() does not work for me.

       

      Please find superstore example.

       

      Great thanks for help in advance.

      Andrew

        • 1. Re: Share of total for month depending on filter
          Simon Runc

          hi Andrew,

           

          I think FIXED LoDs are the best way to go here as they are computed before any regular filtering is applied, so will work how you want.

           

          I first created the share of sales for the selected cities

          [% of Sales for Selected]

          SUM([Sales])

          /

          SUM({FIXED DATETRUNC('month',[Order Date]): SUM([Sales])})

           

          where SUM([Sales]) is respondent to "regular" filters and SUM({FIXED DATETRUNC('month',[Order Date]): SUM([Sales])}) isn't

           

          I can then create the unselected like this

          [% of Sales for Not Selected]

          1-[% of Sales for Selected]

           

          and then just stack them as measures.

           

          There are a few different ways we could do this, but this is probably the simplest.

           

          Hope that helps and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Share of total for month depending on filter
            Jim Dehner

            Hi

            Please see attached

            Yes you need to use an lod

            this is the formula for the selected city - the denominator will return the monthly total sales

            the numerator the sales for the selected sity

             

            the formula for Other cities is simply

             

            it returns this

             

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful