Forum Discussion

AxelKAp's avatar
AxelKAp
Helper I
3 years ago

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 !

Need Help

7 Replies

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

    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's avatar
      AxelKAp
      Helper I
      3 years ago

      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's avatar
        MAwwad
        Solution Sage
        3 years ago

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