4 Replies Latest reply on Aug 20, 2018 1:58 AM by Tapio Schmidt-Achert

    Show and calculate percentages in histogram for a calculated field

    Tapio Schmidt-Achert

      Hi all,

       

      I am currentry trying to show and calculate percentages instead of absolute numbers for a histogramm based on a calculated field. I used this approach from Matt Luton where I calculate count the organizations with:

       

       

      IF FIRST()==0 THEN SIZE() END.

       

      How can I achieve to calculate the percentages? I tried with window_sum or LOD expressions but unfortunately without success.

       

      Thx a lot for your help.

       

      Unbenannt.PNG

        • 1. Re: Show and calculate percentages in histogram for a calculated field
          Michel Caissie

          Tapio,

           

          You can get the % with the following;

          [Organization Count] / MIN({EXCLUDE [Organization]:COUNTD( [Organization] )} )

           

          where

          {EXCLUDE [Organization]:COUNTD( [Organization] )}

          gives you the total number of organization in the view after filtering.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Show and calculate percentages in histogram for a calculated field
            Tapio Schmidt-Achert

            Hi Michel,

             

            thank you very much Michel Caissie that was the solution I was looking for. I got an error message because of not using the

             

            Now i have a second problem: I would like to Filter on Organization Count, e.g. only values bigger than 1 and still want the result to be correct. I would have added a filter to context to achieve that, but as Organization count is a calcultated field I can't add it to context. Do you have an idea to achieve this?

             

            Unbenannt.PNG

            • 3. Re: Show and calculate percentages in histogram for a calculated field
              Michel Caissie

              Tapio,

               

              I haven't forgot you, I was kinda busy and I couldn't put the finger on a solution.

               

              Currently the way you compute the organization count using the size() function gives you some limitations when you use it as a filter.

              As you say, you cannot add it to context, but mostly, because of the order of operation , any other table calculation is computed before this

              table calculation filter is applied.  So even if you filter out the organization count lower than 2,  the window_sum of organization would still

              return  10 instead of 8.

               

              So the goal here is to get the organization count without using any table calculations, and for this we need the Percent Bin to be a dimension.

              We can get this by first computing the percent with the following  lod  calculation.

              {FIXED [Organization],[Measure Number],DATETRUNC('quarter', [Reporting Period Start Date]) : SUM([Numerator])/(SUM([Denominator])-SUM([Exceptions])-SUM([Exclusions]))}

               

              then we compute the Bin

              IF [Percent (mc)]<=.8  Then "<80%"

              ELSEIF [Percent (mc)]<=.85 Then "85%"

              ELSEIF [Percent (mc)]<=.9 Then "90%"

              ELSEIF [Percent (mc)]<=.95 Then "95%"

              ELSEIF [Percent (mc)]<=1 Then "100%"

              End

              and we convert it to Dimension.

               

              From there we can get the organization count with the following lod

              {FIXED [Percent Bins (mc)]: COUNTD( [Organization] )}

               

              So now by using the organization count as a filter, since it does not contain any table calculation , we can get the ratio with

              MIN([Organization Count (mc)]) /WINDOW_SUM( MIN([Organization Count (mc)]))

              compute Table Across

              Now the WindowSum only gives the number of organization in the view.

              note: the MIN could be replace by MAX or AVG  but not SUM because it is a lod and you have the value for every row so you dont want to Sum those.

               

              Make sure to add  Measure Number  and  Quarter(Reporting...)  to context. Since the Percent is now computing using a FIXED lod, we need

              this calculation to compute after the filtering of those two dimensions.  This will occur by adding them to context.

               

              Michel

              1 of 1 people found this helpful
              • 4. Re: Show and calculate percentages in histogram for a calculated field
                Tapio Schmidt-Achert

                Hi Michel,

                 

                thank you very much for your solution, I already applied it to my own data and it works like a charm.

                 

                I had the same idea to calculate without table calculation but did not know how to. Thank you very much for helping me out with this problem.

                 

                Tapio