Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Powerbiuser1234
Regular Visitor

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

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.

 

 

1 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

View solution in original post

7 REPLIES 7
gmsamborn
Super User
Super User

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.

 

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

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

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.

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

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.