14 Replies Latest reply: Nov 29, 2017 2:37 AM by Sunny Talwar RSS

    Return Sales and Share for Product's Top Selling Country

    Benjamin Asher



      I have a table listing products and have created a measure which returns the country with the most sales in a given year:


      =FirstSortedValue(Country,-aggr(sum({<Year={"$(=vSelectedYear)"}>}[Sales Volume]),[Product],Country))


      This then creates a table listing products in one column and their top selling country in the next.


      However, what i am struggling to do is return the sales volume itself for that country in that given year. I thought i could replace Country for [Sales Volume] at the start, but that doesn't seem the case.


      I would also like to then calculate the product's market share for that same country and year. Market share being sales of the product over sales of all products for that country and year.


      I assume annual growth should be straight forward once i can get the sales volume coming through, with the addition of $(=vSelectedYear-1)


      Any support anyone can provide is appreciated!

        • Re: Return Sales and Share for Product's Top Selling Country
          Benjamin Asher

          Ok, i've managed to return the sales for the top selling country:


          max(aggr(sum({<Year={"$(=vSelectedYear)"}>}[Sales Volume]),[Product],Country))


          However, this isn't going to help me calculate the growth, because a max for vSelectedYear-1 will presumably return the volume for the Max country for the year before, which is not necessarily the same.


          Still need to work out how to get total country sales for the vSelectedYear as well, so i can get market share.

            • Re: Return Sales and Share for Product's Top Selling Country
              Mohammed Mukram Ali



              I can give you some suggestions and i hope it will help you to achieve the result.


              Step 1 :  highest [Sales Volume] by Product and Country for the Max Year


                                      =Aggr(Sum({<Year={'$(=Max(Year))'}>}[Sales Volume]),Product,Country)

              Step 2 : To Get the Product & Country name for the Highest [Sales Volume] ( for the Above Query)

              For Product

                                  =FirstSortValue(Product , -Aggr(Sum({<Year={'$(=Max(Year))'}>}[Sales Volume]),Product,Country))


              For Country :

                                  =FirstSortValue(Country , -Aggr(Sum({<Year={'$(=Max(Year))'}>}[Sales Volume]),Product,Country))


              You already got it,

              You got highest sales for current Year and Product and Country Names.


              if you are looking to get the Sales volume for same product and Same country for the last Year.

              then you can do something like store the CountryName and Product into Variables .


              Like :


              vProduct      = FirstSortValue(Product , -Aggr(Sum({<Year={'$(=Max(Year))'}>}[Sales Volume]),Product,Country))

              vCountry      = FirstSortValue(Country , -Aggr(Sum({<Year={'$(=Max(Year))'}>}[Sales Volume]),Product,Country))

              Then use this Variables in Set Analysis to calculate forthe Last Year


              Sum({<Year={'$(=Max(Year)-1)'},Product={'vProduct'},Country={'vCountry'}>}[Sales Volume])



              If you need something do let me know.