0 Replies Latest reply: Aug 23, 2018 6:27 AM by Kimmo Kuopanportti RSS

    How to exclude values from a Bar Chart (based on the sum of Measure over a Dimension)

    Kimmo Kuopanportti

      Hi,

       

      this is a follow-up question to an earlier question from me, for which Leslie Blumenfeld gave the Correct Answer. Both are attached below.

       

      My follow-up question: I would like to draw a Bar Chart where the Dimension is the Posting Year and the Measure is the Costs, but I would like to exclude the values of all Project IDs for which the sum(Costs) = 0. In other words, looking at the example table from my original question, I would like to exclude Project ID "A" from the Bar Chart, because the sum of Costs for that is zero.

       

      One observation: typically an Order ID has costs over several Posting Years (this fact is not displayed in my example table). What I have so far done is that I have aggregated the Costs by Order IDs and then posted them to the earliest Posting Year of each Order ID. Then I have shown the result in a Bar Chart. Indeed, the problem now is that I would like to exclude such Order IDs which belong to a Project ID having sum(Costs) = 0.

       

      Thanks for any help in advance!

       

      Kind regards,

      Kimmo

       

       

      ORIGINAL ANSWER (from Leslie):

       

      I verified that the formula below works. Use it as the dimension for Project ID and be sure to uncheck "Include null values".

       

      =Aggr(If(Sum(Costs) > 0, Only([Project ID])), [Project ID])

       

      (Note: If you also want to allow total project values less than zero, then change the ">" to "<>".

       

      It will also ensure that the filter bar shows "Project ID" if you click on that column for selection.

       

      ORIGINAL QUESTION (from me):

       

      Hi,

       

      I tried to find an answer to this from the existing discussions, but couldn't find a match.

       

      From a bunch of different kinds of postings over a range of several years, I have successfully aggregated the Costs by Order IDs. Now I would need to exclude Order IDs for which the total Cost is zero when they are grouped by Project IDs.

       

      In the example table below, the Order IDs A1 and A2 should be excluded, because the total Costs is zero when they are grouped by Project IDs (+5 -5 = 0). The Order IDs B1 and B2 should be visible, because for them the sum of Costs by Project ID is +3 -2 = +1, which is of course <> 0.

       

      Thanks for any help in advance

       

      Br

      Kimmo

       

      Project IDOrder IDPosting YearCosts

      A

      A12017+5
      AA22018-5
      BB12017+3
      BB22018-2