5 Replies Latest reply: May 30, 2017 5:55 AM by scotly victor

# How to calculate a dimension

Hello,

I have a data set that looks like this

Country          Product      Sales

A                     X               1000

A                     Y               100

B                      X               200

B                      Z               2000

I want to create a dimension which displays which product is the most sold per country

the result would be: product X is the most sold for country A and product Z for country B

it needs to be a dimension, not a measure, ideally I would do this in data loading the script

thank you

LM

• ###### Re: How to calculate a dimension
```Data:
[
Country,Product,Sales
A, X, 1000
A, Y, 100
B, X, 200
B, Z, 2000
];

Temp:
Load Country, Product, SUM(Sales) as TotalSales Resident Data Group By Country, Product;
Left Join (Temp)
Load Country, Max(TotalSales) as MaxTotalSales Resident Temp Group By Country;

New:
Load Country, Product, 1 as MaxSalesFlag Resident Temp Where MaxTotalSales = TotalSales;

Drop Table Temp;
```
• ###### Re: How to calculate a dimension

try this:

a:

Country,

Sum(Sales) as SalesMax

resident your_table

group by Country;

left join

Country,

Sales as SalesMax

Product

resident your_table;

• ###### Re: How to calculate a dimension

Main_tmp:

Country,

Product,

Sales

FROM [Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);

left join(Main_tmp)

Country,

max(Sales) as BestSales

resident Main_tmp

group by Country;

Main:

NoConcatenate

if(Sales=BestSales,1,0) as [Flag Most Sold],

Country,

Product,

Sales

Resident Main_tmp;

drop Table Main_tmp;

• ###### Re: How to calculate a dimension

thank you, but this is too complicated for me and also of course my data structure is much more complicated than in the initial post, for example I have the same table on a monthly basis, so the question is actually "what is the most sold product per country in a certain month etc"

is there a way to calculate this in a visualization then, as a measure perhaps?

I was thinking using FirstSortedValue or something? I tried but it does not work, I get some "-" results for some records

thank you

• ###### Re: How to calculate a dimension

Hi Liv ma ,

Table1:

Country  ,        Product ,     Sales

A      ,               X    ,           1000

A    ,                 Y    ,           100

B    ,                  X   ,            200

B     ,                 Z      ,         2000

];

FinalTable:

load Country,FirstSortedValue(Product,-Sales) as MaxSoldProduct resident  Table1 group by Country;

drop table Table1;

You can use MaxSoldProduct as Dimension