Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional with icons

Good morning community: I have the following problem:

Make a matrix table and you would need to add conditional icons to it. The problem is that each icon must indicate whether the amount in percentage of the measure is greater or less than the previous one. It is a Matrix that shows a percentage measure for each week, and the idea is that if the following week the percentage increases, it shows an arrow icon upwards, otherwise downwards, and so on. Illustrative image attached.

Can it be achieved? Thanks!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    You can try formula like the following:

    calculated column:

    Rank_semana = RANKX('Table','Table'[semana],,DESC,Dense)

    measure columns:

    following week =
CALCULATE (
    [Uso_cel],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Rank_semana]
            = SELECTEDVALUE ( 'Table'[Rank_semana] ) - 1
            && 'Table'[prov_nombre] = SELECTEDVALUE ( 'Table'[prov_nombre] )
    )
)

    indicate =
IF (
    ISBLANK ( [following week] ),
    BLANK (),
    IF ( [Uso_cel] < [following week], UNICHAR ( 9650 ), UNICHAR ( 9660 ) )
)

     

    Hope this can work for you.

     

    Best Regards,
    Community Support Team _ Eason

     

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    You can try formula like the following:

    calculated column:

    Rank_semana = RANKX('Table','Table'[semana],,DESC,Dense)

    measure columns:

    following week =
CALCULATE (
    [Uso_cel],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Rank_semana]
            = SELECTEDVALUE ( 'Table'[Rank_semana] ) - 1
            && 'Table'[prov_nombre] = SELECTEDVALUE ( 'Table'[prov_nombre] )
    )
)

    indicate =
IF (
    ISBLANK ( [following week] ),
    BLANK (),
    IF ( [Uso_cel] < [following week], UNICHAR ( 9650 ), UNICHAR ( 9660 ) )
)

     

    Hope this can work for you.

     

    Best Regards,
    Community Support Team _ Eason

     

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You need to create a YOY measure and that you can use in icon formatting under conditional formatting

     

    measures like

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ]) //YOY

     

    Last one can be done without date table

     

    refer if needed

    https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
    https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692