Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following datatable and I want to display a table with one row per store and get the sum of UnitSales for the store in one column and in a second column the sum of UnitSales for the country of the store.
ProductData:
LOAD * inline [
Store|Country|UnitSales|UnitPrice
Berlin|GER|4|16
Berlin|GER|3|14
Stuttgart|GER|10|15
Wien|AUT|9|9
Salzburg|AUT|5|10
Bern|SUI|2|20
Basel|SUI|25|25
] (delimiter is '|');
I tried something like Sum(TOTAL Aggr(Sum(UnitSales),Country, Store)) or Sum(TOTAL Aggr(Sum(UnitSales),Country)).
Hi,
If you are looking to achieve one line for every store and same for country in frontend, you can directly take straight table and put Dimensions/Measures. It will be shown as expected.
I found a solution with the following formula, but I hope there is a more elegant/dynamic way:
if(Country='GER', Sum({$< Country={'GER'} >}TOTAL Aggr(Sum(UnitSales),Country)),
if(Country='AUT', Sum({$< Country={'AUT'} >}TOTAL Aggr(Sum(UnitSales),Country)),
if(Country='SUI', Sum({$< Country={'SUI'} >}TOTAL Aggr(Sum(UnitSales),Country)),
Sum(TOTAL Aggr(Sum(UnitSales),Country)))))
table with sample values