Filter by thresholds

[Anonymous]

Hi everyone! 

I want to filter in Power BI by thresholds.

The filter should be able to show the following:

If threshold value is above 3 (per month) then set highlighting
and
If threshold value is above 5 (in 3 months) then set highlighting

But how can I filter this?
As you can see in the picture, numbers are also highlighted there, although they were below the threshold in the respective months. (e.g. number 7391886)

Can you help me to filter this correctly?

Thanks a lot for your help!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to make conditional formatting

Conditional formatting =
VAR _tab =
    SUMMARIZE ( 'Table', 'Table'[date].[Month], "@qty", SUM ( 'Table'[qty] ) )
VAR _qty =
    SUMX ( _tab, [@qty] )
VAR _count =
    COUNTROWS ( FILTER ( _tab, [@qty] > 3 ) )
RETURN
    IF (
        _count > 0,
        IF (
            (
                ISINSCOPE ( 'Table'[order] ) || ISINSCOPE ( 'Table'[item] )
                    || ISINSCOPE ( 'Table'[date] )
            ),
            IF ( _qty > 3, "red" ),
            IF ( _qty > 5, "red" )
        )
    )

2. Configure conditional formatting

If the above one can't help you get the expected result, please clarify which numbers in your screenshot meet those two conditions you mentioned? And in order to provide you with a suitable solution, could you please provide some sample data(exclude sensitive data) and the Fields settings of your matrix. If there are measures involved, please provide their formulas. Thank you.

---

If threshold value is above 3 (per month) then set highlighting
and
If threshold value is above 5 (in 3 months) then set highlighting

---

Best Regards

[amitchandak]

@Anonymous , You can create a measure like this example and force different conditional formatting in row and total using field value option

if( not(isinscope('Date'[Month])),
Switch( true() ,
[Measure ] < 5 , "Red", "White") ,
Switch( true() ,
[Measure ] < 3, "Red", "White")
)

In case you need 3 month measure

Rolling 3 =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-3,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

or

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Then create a color measure

if( not(isinscope('Date'[Month])),
Switch( true() ,
[Rolling 3 ] < 5 , "Red", "White") ,
Switch( true() ,
[Rolling 3 ] < 3, "Red", "White")
)

How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

icon

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/