Return Sales and Share for Product's Top Selling Country

[lmcbasher]

Hi,

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!

[lmcbasher]

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.

[mdmukramali]

Hi,

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.

Thanks,

Mukram.

[lmcbasher]

Hi Mukram,

Thanks for help. I did try the variable route once i'd found the top country, but sadly i'm just getting zeroes. If i hard code the country i get the correct value and the variable returns the right country. However, combining both doesn't seem to work.

Still struggling to return total sales across all products for that country too, so i can determine the product's market share. Although i believe i can get that in the set analysis with TOTAL if i can get the variable working.

Any other ideas? Why's my variable with FirstSortValue() not working with set analysis?

Thanks again!

[lmcbasher]

Hi Mukram,

Just realised i had not created a variable with the FirstSortValue() to get the top country but a measure. It's a learning curve! If i actually create a variable then i can get it working, although i use the syntax:

Country={"$(=vCountry)"}

However, this only works once i have selected a product, but i want to be able to show this in a table with the products listed on the left. This being the case, i don't think the variable route is going to work, is it?

[mdmukramali]

Hi Benjamin,

Can you share the sample Data Application so that i can help you in better way,

Thanks,

Mukram.

[mdmukramali]

Updated Formula:

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

[lmcbasher]

Hi Mukram,

I've attached a sample of what i'm trying to do here.

Below is a snapshot of the table. Far left are the products, then total sales across all countries, then number of countries that each product is in, then the Top Sales Country (using FirstSortedValue()) and then the Top Sales Country Sales (using Max(aggr())). So far so good.

To then be able to get the growth for that product in the top selling country and to be able to return its country share i was trying to use a variable for the FirstSortedValue() to then be able to insert it into a Set Analysis. I have added a column returning the result of the variable (Top Sales Country (By Variable)) to show it is working. I've then added another column called Top Sales Country Sales (By Variable) which via set analysis should return the same figure as the MAX() earlier for the Top Sales Country Sales. Sadly it does not.

It appears what while this works when a selection is made (except for the share one), it doesn't work for a chart table as it is. I believe the variable just returns result for the last item in the table and not for each row.

I fear the variable route is not the way to go with this now!

Any ideas?!

Many thanks!!!

[mdmukramali · Accepted solution]

Hi Benjamin,

For the Previous Year Sales for the same Country i used,

=FirstSortedValue(Aggr(Sum({<Year={'$(vPreviousYear)'}>}[SALES VOLUME]),PRODUCT,COUNTRY),

-Aggr(Sum({<Year={'$(vSelectedYear)'}>}[SALES VOLUME]),PRODUCT,COUNTRY))

Kindly find the attached App for the reference .

i hope it will help you to resolve your issue,

Thanks,

Mukram.

[lmcbasher]

Hi Mukram,

Genius! That works perfectly. I imagine it will grind on my cores a bit, but i think it's worth it.

Any ideas on how to return the total sales for that top market? That would be over all products. That way i can work out the share of that product in its top market. That's the last thing i need to work out for this.

I've tried chucking in a TOTAL here and there in your new FirstSortedValue() formula , but it's not having it. I'm struggling to get my head around how it works and so a TOTAL may not be appropriate in this case.

Many, many thanks for the help. It's a big help.