7 Replies Latest reply on Aug 2, 2018 8:39 PM by tian.yang.4

    calculation help

    jon rios

      here is a screen shot. i i want to isolate the highlighted number, i assumed this formula would do it but it doesnt, what am i doing wrong?


        • 1. Re: calculation help
          Ken Flerlage

          Any chance you can share some sample data or a packaged workbook? It's difficult to diagnose this problem without them, unfortunately.

          • 2. Re: calculation help
            meenu choudhary

            Hi Jon,


            I have tried above scenario on superstore data and it is giving me correct result.


            if ([Category])="Furniture" and ([Sub-Category])="Tables"

            THEN "FALSE" else "TRUE"



            • 3. Re: calculation help

              Hi Jon


              you can try this way, i use sample-superstore



              • 4. Re: calculation help
                jon rios

                thanks, I should of been more specific, by isolate, i mean, be able to bring 1 calculation to the shelf as a measure and it shows this particular value. So i assume it has to be a {FIXED} expression, but i have tried every combination and cant get this .1410, if I use this expr.  i get Null


                {FIXED [Period Date], [Month Qtr], [Benchmark + Portfolio (RS)], [SegmentLabel1], [SegmentLabel2]:

                sum (if [SegmentLabel1]= "Now 2 CPD" and [SegmentLabel2] = "Were 1 CPD" THEN [DeltaMetrics]

                END ) }


                i assumed if i replace this Measure (in this screenshot), with my calculation, i would be the .1410 and nothing else, BUT everything goes blank (NULL)


                i tried this adding more filtering conditions, but my quarter date part is incorrect...


                {FIXED [Period Date], [Month Qtr], [Benchmark + Portfolio (RS)], [SegmentLabel1], [SegmentLabel2]:

                sum (if [SegmentLabel1]= "Now 2 CPD" and [SegmentLabel2] = "Were 1 CPD"

                        and DATEPART('year',[Period Date])= 2017 and DATEPART('quarter', [Period Date]) = Q3

                        and [Month Qtr]= "Q" THEN [DeltaMetrics]

                END) }


                • 5. Re: calculation help
                  Zhouyi Zhang

                  Hi, Jon


                  It is not easy to give a straight forward advice without seeing a sample workbook.


                  But please try below


                  Hope this helps, otherwise, please prepared a sample workbook, it doesn't have to be your real data, just some make up.



                  • 6. Re: calculation help


                    hi Jon,


                    I use your formula and it works.

                    I think the problem is  you cannot fix the column which is a measure--[Benchmark+Portfolio (RS)]

                    {fixed+dimension:sum(measure)}  the dimension determines at what level measure should be aggregated.


                    if you problem is still there maybe you can send me the workbook.



                    • 7. Re: calculation help


                      hi Jon


                      i misread the Benchmark as a measure >_<


                      i build a similar vision and it works

                      The little different here is the Date , maybe you can first delete it and see whether it works and then put it back.

                      Datepart or Datetrunc it has sequence


                      i hope this time your problem can solve.