Sum or count of values within first dimesion

[#1]

Hi,

I have an issue with getting the sum or count of distinct values within the first of two dimensions in a table.

I have this table:

Country	Store	Sales Store	Sales Country
USA	Store 1	100	300
USA	Store 2	100	300
USA	Store 3	100	300
UK	Store 1	200	600
UK	Store 2	200	600
UK	Store 3	200	600

The last column, "sales country" is what I am trying to get.

Can anyone help me with an expression that shows me that? I have tried with set analysis and saying TOTAL, but that gives me the total of all countries in the list, unless i filter first. Is there a way of saying (like in excel) sumif(A:A;A2;C:C) ?

Thanks a lot for your help!

Best regards,

Claus

[goncalo_ricardo_pereira]

Hello,

You can try something like this:

sum(TOTAL<Country> [Sales Store])

The "Total<Country>" basically is saying to the expression, ignore all the dimensions except the Country.

Best Regards

[simondachstr]

Use the below expression

=aggr(sum([Sales Store]),Country)

[Anonymous]

[#5]

Hi all - thanks, those worked for the sum!

Now i would like to do the same for count of distinct product_id in the country, not the store. Is that a possibility?

I tried this

Count(TOTAL DISTINCT <Store_CountryName> [Product_DesignVariationID])

but it only shows the value for 1 of the stores, and i would like it to show the value on all the stores.

Thanks!

[#6]

Hi - thanks, that worked for the sum!

Now i would like to do the same for count of distinct product_id in the country, not the store. Is that a possibility?

I tried this

Count(TOTAL DISTINCT <Country> [Product_Id])

but it only shows the value for 1 of the stores, and i would like it to show the value on all the stores.

Thanks!

[goncalo_ricardo_pereira]

Hello,

Try This:

count(TOTAL<Country> DISTINCT [Product_Id])

Best Regards

[#8]

FANTASTIC that worked - thanks!