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

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

Reply
AxelKAp
Helper I
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
MAwwad
Super User
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.

Hi @MAwwad , 

Thanks for you reply ! 
For the table thresholds, I have created this table because I didn't have this table before:

AxelKAp_0-1677854620289.png

And for the below thresholds I have this formula:

AxelKAp_1-1677854709723.png

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

 

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

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

Send a copy PBIX 

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

A123samedi 1 janvier 2022570 000 €Supplier A
B123samedi 1 janvier 202220 000 €Supplier B
C123samedi 1 janvier 2022123 000 €Supplier C
D123samedi 1 janvier 202280 000 €Supplier D

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

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 : 

AnthNC_0-1714355108206.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.