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 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!

 

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super 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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    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