6 Replies Latest reply on Feb 17, 2016 1:30 PM by Robert Yu

    LOD to find median.

    Robert Yu

      Hello,

       

      I have some data:

       

       

      InstDest_GroupDest Cipn1N
      I1Ca43
      I1Fa63
      I1Ua54
      I1Cb96
      I1Fb3
      I1Ub78
      I1Cc12
      I1Fc65
      I1Uc49
      I2Ca3
      I2Fa47

      etc...

       

      My worksheet is set up so that [Dest Cipn1] is a row, and [Dest Group] is a column. They display [value] as a bar chart. [value] = {include [Inst] : sum([N])} / {fixed [Inst] : sum([N])}

       

      This worksheet is filtered on [Inst] = I1. I would like to add a reference line that shows the median value for each bar (cell) across all the [Inst]. (In the end I will add a band that displays 25th - 75th percentile but I figured working with the median would be simpler first).

       

      I thought this would work, but it doesn't: [AllInstMedian] = {fixed [Inst],[Dest Group], [Dest Cipn1] : Sum([N])} / {fixed [Inst] : Sum([N])}

       

      Any suggestions? I'm attaching a sample workbook hoping that helps.

       

      Thank you

       

      I should note (it may affect aggregation) that in my data there there's on more column, such as

       

      Inst, Dest_Group, Dest Cipn1, N, Othercolumn

      I1, C, a, 52,a

      I1, C, a, 23, b

        • 1. Re: LOD to find median.
          Steve Mayer

          Robert -

           

          I'll start by saying that as tempting as it becomes to use LOD expressions to solve everything, there are still cases where we have to go old school. Let's start with confirming definitions: median in your use case is the middle [Value] in your data set - since there are 27 values across all [Inst], the Median is the 14th Value (see screenshot below).

           

          If that is the median you are looking for, I can't see a way of determining the median using an LOD expression (I'm pretty good with LOD, but someone out there, please prove me wrong), because LOD values are not in any particular sort order. You would have to be able to index the values in order to find the middle one.

           

          So I used the old school table calculation using WINDOW_MEDIAN, and the LOOKUP trick to make sure that no [Inst] were filtered out of the resulting Viz, because you want the median value to be across all [Inst].

           

          Step 1

          Remove the [Inst] filter, because we need all values in the data source to find the Median

           

          Step 2

          Create a calculated field [Median]

          WINDOW_MEDIAN(MAX([Value]))

           

          Step 3

          Add [Median] to the Level of Detail of your Viz, and use the Advanced Table Calculation dialog to move everything to Addressing (no Partitioning because you want the Median across all values)

           

          Step 4

          Add [Median] as a reference line to your chart

           

          Step 5

          Create a calculated field [Inst Lookup]

          LOOKUP(MAX([Inst]), 0)

           

          Step 6

          Add [Inst Lookup] to filters and set it to "Inst1"

           

          Step 7

          Confirm that Median is still the median for the entire data source

           

          Workbook attached.

           

          • 2. Re: LOD to find median.
            Robert Yu

            Steve,

             

            Thanks for looking into this. I am actually trying to find the median across [Inst] for each cell. Thus there would be a different median for each bar. In my example data, it would be the median of (.092873, .007614, .02514)... so .02514 for the first bar. Do you think that would require LOD? Maybe I am over complicating it. I assumed I would need LOD because my dimension filter is [Inst] = I1 but I want to find a median across a broader selection (all the inst) for each [Dest_Group] [Dest Cipn1] bar cell.

             

            Does that make sense?

            • 3. Re: LOD to find median.
              Steve Mayer

              I'm confused by your definition of median.

               

              Over what range of values is the median? Do you expect a different median for each Dest Group / Dest Cipn1 combination, thus a different reference line for every single bar? Is this what you are looking for?

               

               

              The above solution should give you what you are looking for - it is just down to setting the correct addressing/partitioning to determine how to calculate the median.

               

              -Steve

              • 4. Re: LOD to find median.
                Robert Yu

                Yes I do. What happened to your last reply? I think that was what I was looking for (thank you) (sorry I'm taking a little time processing it). I just changed the ref line from per pane to per cell.

                • 5. Re: LOD to find median.
                  Steve Mayer

                  Here is the workbook with the complete solution. I also changed the reference lines from Per Pane to Per Cell, and have medians for every bar across all Inst.

                   

                  The key to the whole thing is the table calculation filter - even though it filters only Inst1 values, the Median table calculation still calculates over the entire data source.

                   

                  Still don't think this is possible with LOD calculations (but challenge anyone to find a way!), so hopefully this is a workable solution.

                   

                  -Steve

                  2 of 2 people found this helpful
                  • 6. Re: LOD to find median.
                    Robert Yu

                    Steve - Thanks so much! Yes the lookup trick and the table calculation filter really bring everything together - I'll have to look more into those