6 Replies Latest reply: Apr 7, 2017 11:01 AM by omar bensalem RSS

    Calculated measures

    Mike Slottje

      Hi guys,

       

      it would be great if you could help me out with the following situation.

       

      I have i.e. sales data of the years 2016, 2015 & 2014 in 2 different categories.

      Now I want to calculate what their sales are going to be in 2017, based on a growth %.

      I've attached a file as an example.

       

      I need a function for the following 4 calculated measures

      1 = * if Category = A, and 2014, 2015 & 2016 > 0 then AVG 3 years * 1.2

      2 = * if Category = A, and revenue <0 in 2014 but > 0 in 2015,2016 - then 2016 * 1.2

      3 = * if Category = A and revenue in 2016 <= 0, then show 0

      4 = * if Category = A and revenue in 2015 <= 0 and revenue in 2016 > 0, then show avg 2014-2016

       

      Could you guys help me out?

       

      Cheers,

      Mike

        • Re: Calculated measures
          omar bensalem

          Based on your example data :

           

          if(sum({<Category={'A'}>}[Revenue 2014])>0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,

          (sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2015])+sum({<Category={'A'}>}[Revenue 2016]))/3

          ,

          if(sum({<Category={'A'}>}[Revenue 2014])<0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,

          sum({<Category={'A'}>}[Revenue 2016])*1.2,

          if(sum({<Category={'A'}>}[Revenue 2016])<=0 ,0,

          if(sum({<Category={'A'}>}[Revenue 2015])<=0 and sum({<Category={'A'}>}[Revenue 2016])>0,

          (sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2016]))/2

          )

          )

          )

          )

            • Re: Calculated measures
              omar bensalem

              If your question has been answered, please don't forget to close the thread by marking the correct answer as so.

              Thanks,

              • Re: Calculated measures
                Mike Slottje

                Thanks for your reply Omar! I was still testing haha. I will mark the correct answer if I have everything working!:)

                 

                Unfortunately, I'm still getting NULL values when I'm using the next part of your code.

                (if(sum({<Category={'A'}>}[Revenue 2014])<0

                and sum({<Category={'A'}>}[Revenue 2015])>0

                and sum({<Category={'A'}>}[Revenue 2016])>0,

                sum({<Category={'A'}>}[Revenue 2016])*1.2)

                 

                And what should I do if I want to add the same formulas for Category B to this formula, but with category B * 1.1?

                  • Re: Calculated measures
                    omar bensalem

                    Can you share the qvf file (your app) you're working with?

                      • Re: Calculated measures
                        Mike Slottje

                        I can't do that unfortunately, because the data that we use is confidential for our company..

                         

                        What I'm searching for, is a formula that says what you already explained, but with a second if statement (if category = B). That could be the same formula, but I can't get the multiple if statements working.

                         

                        Sorry for the inconvenience..

                          • Re: Calculated measures
                            omar bensalem

                            You simply choose a chart,

                            as dimension: category:

                            as measure:

                            if(sum([Revenue 2014])>0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

                            (sum([Revenue 2014])+sum([Revenue 2015])+sum([Revenue 2016]))/3

                            ,

                            if(sum([Revenue 2014])<0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

                            sum([Revenue 2016])*1.2,

                            if(sum([Revenue 2016])<=0 ,0,

                            if(sum([Revenue 2015])<=0 and sum([Revenue 2016])>0,

                            (sum([Revenue 2014])+sum([Revenue 2016]))/2

                            )

                            )

                            )

                            )

                             

                            result :

                            I'm working with :

                            Capture.PNGCapture.PNG