Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mattyp2021
Regular Visitor

Power BI - Conditional Formatting

Hi,

 

Wondering if anyone is aware of how to do this. I have a series of information put into a matrix table using only three columns. Name, Month and Percentage. I need to add conditional formatting in terms of icons for up and down arrows which identify whether the previous precentage for the month before was higher or lower.

 

mattyp2021_0-1631026460630.png

 

Does anyone know of an easy method of doing this?

 

Thanks,

 

Matt

1 ACCEPTED SOLUTION

Hi @mattyp2021 ,

 

Please create a Date Table and the relationship:

 

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy" ),
    "MonthNum", FORMAT([Date], "yyyymm")
)

vkkfmsft_0-1631251110092.png

 

Then create the measure:

 

Measure = 
var pre_month = 
    CALCULATE(
        SUM('Table'[Audit Score]),
        FILTER(
            ALL('Date'),
            'Date'[MonthYear] = FORMAT( EDATE( MAX('Date'[Date]), -1 ), "mmmm yyyy" )
        )
    )
var diff = SUM('Table'[Audit Score]) - pre_month
return 
    IF(
        diff <> BLANK(),
        IF(
            diff < 0,
            "ColoredArrowDown",
            "ColoredArrowUp"
        )
    )

icon.pngvkkfmsft_1-1631251284933.png


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@mattyp2021 , create a diff or diff % and use that in coditional icon formatting

 

MTD = CALCULATE([percent],DATESMTD('Date'[Date]))
last MTD = CALCULATE([percent],DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

diff = [MTD]-[last MTD]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

Or you can use custom icon meausre using unichar and color in coditional formatting usinf field value

 

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

https://exceleratorbi.com.au/dax-unichar-function-power-bi/

Arrow = // to be used as measure
var _change =[MTD]-[Last MTD]
return
SWITCH (
TRUE(),
_change > 0, UNICHAR(9650),
_change = 0, UNICHAR(9654),
_change < 0, UNICHAR(9660)
)


/////Arrow Color
Arrow color =
var _change =[MTD]-[Last MTD]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak 

What would be the easiest solution? My raw data is in 3 columns like the screen shot below.. The month section goes as far as January 2021 to August 2021 and I need the audit score to look at the previous audit score to determine whether it's higher or lower than the one before then display an up icon or down icon.

mattyp2021_0-1631034786009.png

 

In this instance below we have three users with 3 months worth of percentages. I now need to get a higher or lower icon next to each to reflect an improvement or decrease from the previous month.

 

Kind regards,

 

Matt

mattyp2021_1-1631034951198.png

 

 

Hi @mattyp2021 ,

 

Please create a Date Table and the relationship:

 

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy" ),
    "MonthNum", FORMAT([Date], "yyyymm")
)

vkkfmsft_0-1631251110092.png

 

Then create the measure:

 

Measure = 
var pre_month = 
    CALCULATE(
        SUM('Table'[Audit Score]),
        FILTER(
            ALL('Date'),
            'Date'[MonthYear] = FORMAT( EDATE( MAX('Date'[Date]), -1 ), "mmmm yyyy" )
        )
    )
var diff = SUM('Table'[Audit Score]) - pre_month
return 
    IF(
        diff <> BLANK(),
        IF(
            diff < 0,
            "ColoredArrowDown",
            "ColoredArrowUp"
        )
    )

icon.pngvkkfmsft_1-1631251284933.png


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.