Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Does anyone know of an easy method of doing this?
Thanks,
Matt
Solved! Go to 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")
)
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"
)
)
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.
@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"
)
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.
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
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")
)
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"
)
)
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |