Create a threshold filter

[AxelKAp]

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 !

[MAwwad]

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.

[AxelKAp]

Hi @MAwwad , 

Thanks for you reply ! 
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 ? 

Thanks for your time

[MAwwad]

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

[AxelKAp]

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

[MAwwad]

Send a copy PBIX 

[AxelKAp]

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.

[AnthNC]

Hi AxelKAp,

You can't upload files directly in here. You have to generate a url once you've uploaded your pbix file in your onedrive, google drive, etc.

Then use the "insert a link" option :