Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter by thresholds

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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/