cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Create a threshold filter

Hi everyone,

I have a table where all the invoice lines are stored, you can find the following columns:
- Invoice number, Invoice Line, invoice date, entity, country, supplier name, amount in €

I would like to create a filter to display only the suppliers linked to a thresholds. Example:
I would like to be able to filter by:
- <500K€, <250K€, <100K€, ...
And if I choose <500K€ and 2022, I will display only the suppliers with a spend <500K€ in 2022.

Someone has an idea to do this ?

Thanks for you support !

7 REPLIES 7
Super User

Yes, you can create a threshold filter in Power BI using the following steps:

1. Create a new measure that calculates the total spend for each supplier. You can use the following formula:

Total Spend = SUM('Table'[amount in €])

Note: Replace 'Table' with the name of your table.

1. Create a new measure that calculates the total spend for each supplier in a specific year. You can use the following formula:

Total Spend in Year = CALCULATE(SUM('Table'[amount in €]), YEAR('Table'[invoice date]) = SELECTEDVALUE('Calendar'[Year]))

Note: Replace 'Table' with the name of your table and 'Calendar' with the name of your calendar table.

1. Create a new measure that checks if the total spend for each supplier is below a certain threshold. You can use the following formula:

Below Threshold = SWITCH(SELECTEDVALUE('Threshold'[Threshold]), "<100K", [Total Spend] < 100000, "<250K", [Total Spend] < 250000, "<500K", [Total Spend] < 500000, BLANK())

Note: Replace 'Threshold' with the name of your threshold table and adjust the threshold values to your needs.

1. Add a visual filter to your report and select the 'Below Threshold' measure as the filter field. You can also add additional filters for the year and any other fields you want to include.

2. Use the visual filter to select the threshold value and year you want to filter by.

3. The visual will now display only the suppliers whose total spend is below the selected threshold for the selected year.

I hope this helps! Let me know if you have any questions.

Helper I

For the table thresholds, I have created this table because I didn't have this table before:

And for the below thresholds I have this formula:

But I don't see how I can filter with this formula, because when I create a slicer view I can not add the formula.
And second question, how this formula can help to filter the suppliers thresholds ?

Super User

In the Filter Slicer, Use the Column you created that contains the threshhold

Helper I

It's not working, when I take the column Thresholds in the Thresholds table and I filter nothing happen

Super User

Send a copy PBIX

Helper I

I didn't succeed to share the PBIX.
But I can share with you the test table:
Invoice NumberInvoice DateInvoice AmountSupplier Name

 A123 samedi 1 janvier 2022 570 000 € Supplier A B123 samedi 1 janvier 2022 20 000 € Supplier B C123 samedi 1 janvier 2022 123 000 € Supplier C D123 samedi 1 janvier 2022 80 000 € Supplier D

But here it's only a few lines, I have more than 10M of lines.

Helper I

Hi AxelKAp,

Then use the "insert a link" option :

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors