Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
I have table with 3 dimensions:
Month
Client
Country
And 2 measures:
Sales
Number of products
Let's say i have 100 rows in this table.
Each row contains specific match and the numbers are different.
Now i have chosen the current month.
I would like to use the search expression in the Country column to filter sales above 200,000$.
When i use =sum(Sales)>200,000 it shows the total per country meaning sum of all combinations that above 200,000$ but it is not what i would like to get.
How can i use the search expression the get all combinations in the table using the existing dimensions to get sales>200,000$.
For example :
Client a FR 203,456$
Client b DE 567,999$
Client c US 900,233$
And not :
Client a FR 203,456$
Client b DE 567,999$
Client c US 900,233$
Client d FR 115,745$
Client d should not be in the selection.
Best,
Tal
Hi Carlos ,
So it seems to work fine but with one exception.
when i select the current Month and specific Client but he has 2 different rows with different numbers beacouse
he spent it on product a and product b.
With the case above the expression: Sum({<Sales = {"> 200000"}>}Sales) will check the total sales of country and then check if there sum is greater then 200,000$
Thanks for the link ,
I read it before i uploaded my q. It helped me in another case:)
I use the table option and as you well aware the search expression shows only in the dimension column and not in the measure column.
Appreciate it!
Best,
Tal
I think I understand your issue, you need to add a column for product in your table, or if you are only interested in a single product you can add the filter for product in your expression. Something like:
Sum({<Product = {'a'},Sales = {"> 200000"}>}Sales)
Wow great ,
But if i don't show the the product column?
The same advise?
Yes, you don't need to show the product column and still apply the product filter. If you find this to be an appropriate solution please mark it as the correct answer.