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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

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.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.