Use Slicer to filter the page by sum(revenue) of customers

[Powerbiuser1234]

Hi,

i created a report with informatioen about revenue and costs of our customers. I already have a slicer for a year in it. I also want to have a slicer for the cumulated amount of revenue of my customers.

So for example: I want to filter the page by all customers who have a had revenue higher than 10K (The amount of revenue is not one number in my data, it's the sum of all purchases a customers has made). 

How can i create that slicer?

To add an example:

Two tables:
Table 1:
Customer, Customer ID

Table 2:

Customer ID, Revenue, other data (not relevant)
1, 100€

2, 150€

1, 200€

3, 400€

and so on.

For example i now have a bar graph with total revenue of each customer.

I now want a mobile slicer in my report, where i can put the range in for revenvue per account. For example that i can chosse show me in my report page only the customers who have had a total revenue higher than 5K.

Thanks in advance for your help.

[gmsamborn]

Hi @Powerbiuser1234 

In my solution, I added a numeric parameter (Threshold) to specify a lower limit of the report.  A measure like this can be used to filter the visual.

Include = 
    IF(
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    VALUES( 'Customer'[FullName] ),
                    "__Sales", [Sales]
                ),
                [__Sales] >= [Threshold Value]
            )
        ),
        1
    )

Customers over X in sales.pbix

I hope this helps.

[Powerbiuser1234]

Hi, thanks for the advice. I don't think this is what i'm looking for. Maybe i make it more clear with an example:

Two tables:
Table 1:
Customer, Customer ID

Table 2:

Customer ID, Revenue, other data (not relevant)
1, 100€

2, 150€

1, 200€

3, 400€

and so on.

For example i now have a bar graph with total revenue of each customer.

I now want a mobile slicer in my report, where i can put the range in for revenvue per account. For example that i can chosse show me in my report page only the customers who have had a total revenue higher than 5K.

Is it a little bit more clear?

Thanks a lot

[gmsamborn · Accepted solution]

Hi @Powerbiuser1234 

Table 1 is your customer dimension.  In my example it is named 'Customer'.

Table 2 is your fact table.  In my example it is called 'Sales'.

Using the 'Threshold' table and the above measure , I am able to create a barr-chart that has a variable threshold.  ie.  You can specify only customers with sales over 5000.

See the second page of Customers over X in sales.pbix

[Powerbiuser1234]

Hi,

sorry, my bad. I tried it an it worked. Thanks a lot.

One last question: How were you able to create the slicer with just one value and the option to move it to left and right? That option doesn't exist in my report.

[gmsamborn]

In the format visual pane, under Slicer Settings --> Options, change Style to Single Value.

[Powerbiuser1234]

Yes i know, but that option doesn't apperar there. I can't choose it

[gmsamborn]

I'm not sure what to say.  Without seeing your pbix, there isn't much I can do.  Other than looking for differences in settings, I'm not sure what else to do.