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 Tal,
You can use set analysis:
Sum({<Sales = {">= 200000"}>}Sales)
Thanks,
Carlos
Hi Carlos ,
Thanks for your reply:)
It works well with one dimension but when i have 2 dimensions and more it still summarized the total and not the specific row .
Hope it is clear enough.
Do you have any suggestions?
Best,
Tal
Try :
sum({<Client={"=sum(Sales)>=200000"}>}YourMeasure)
or (think it's more intelligent to do as follow)
sum({<Client={"=sum({1}Sales)>=200000"}>}YourMeasure)
Thanks Omar!
I want to cover all options, means that if i'm not filtering month the following can happen:
Client a FR 203,456$ for Jan
Client a FR 137,999$ for Feb
Client c US 900,233$
and then when i use your expression it is still not filtering Feb but summarize and the 2 rows for FR appears.
Best,
Omri
maybe:
Sum({<measure={">=200000"}>}measure)
Hello bi_analysts_2you need to put your expression on column measure sales as follow :
sum({<sales={">200000"}>}sales)
i tried it with sample data and it works when i choose a specific month and country also
Can you share the expression you are using and the kind of chart you are applying it to?
I tried the expression and it works fine regardless of the filters you are applying. If you want to include 200,000 and greater use:
Sum({<Sales = {">= 200000"}>}Sales)
If you want just greater than 200,000 use:
Sum({<Sales = {"> 200000"}>}Sales)
If you share the expression you are using and the chart it is been applied to it would be easier to troubleshoot.
This blog by Henric may help you to understand the sintax:
Thanks,
Carlos