Conditional formatting help

[sarath_chandra]

Hi all,

actually ,above is my matrix visual in my power bi(PN is part number ,and values are usage of part number in every week of (2018,2019,2020,2021....)

i need some help in conditional formatting

1.dynamically it should show the latest week(9/2/2018) - Top 15 usage values.

2.There is lot of empty values are there in matrix values - need to bring zeros in empty places

3.dynamically,every week compare the previous week usage values (i) If the value is higher than previous week value - it should show some different font color or different back ground colour/symbol (ii) If the value is equal to previous week value - it should show some different font color or different back ground colour/symbol (iii)  If the value is less than previous week value - it should show some different font color or different back ground colour/symbol

4.If the usage of PN for continous three weeks - it should show all three values in different color/symbol

can you all please help me in this ? 

[v-zhangti · Accepted solution]

Hi, @sarath_chandra 

You can try the following methods.

Measure:

Current value = SUM('Table'[usage value])+0

Previous value = CALCULATE([Current value],FILTER(ALL('Table'),[Weeknum]=SELECTEDVALUE('Table'[Weeknum])-1&&[PN]=SELECTEDVALUE('Table'[PN])))

Diff = [Current value]-[Previous value]

Color = IF([Diff]=0,"Yellow",IF([Diff]>0,"Green","Red"))

Is this the result you expect? The last question is what kind of output you expect it to be, and can you show it in pictures.

Best Regards,

Community Support Team _Charlotte

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

[sarath_chandra]

every week i will get the data with date ,part numbers and usage values like this format @BeaBF .

please help me out 🙏

[BeaBF]

@sarath_chandra Hi! Can you attack a pbix file with the same sample data above in order to provide you the expected results?

BBF

[sarath_chandra]

Thank you so much for your quick response. Im not able to attach the pbix file in this power bi community . options are not available for me .sorry for inconvience.

above image is in  matrix visual of power bi .

date is in columns 

PN is in rows

values is in value 

[BeaBF]

@sarath_chandra OK, can you paste some sample data in the reply?

BBF

[sarath_chandra]

Date	PN	usage value
05/01/2018	7ui45	2
12/01/2018	8u89	118
23/01/2018	8u89	162
02/02/2018	4567	7
09/02/2018	4567	10
02/02/2018	8u89	82
09/02/2018	8u89	106
23/01/2018	7ui45	6
02/02/2018	7869	4
09/02/2018	7869	4
05/01/2018	8790	13
12/01/2018	8790	31
23/01/2018	8790	73
02/02/2018	8790	18

[BeaBF]

@sarath_chandra ok, let's start from point 2, the blank values.

You have to create a table starting from the native, making a distinct on the Date field, then create a relationship between the two tables and use the Date field of the new table in the matrix. At this point in "values" put a new measure that makes the sum of the usage field +0.

I am attaching a pbix with this point done with example data. Let me know if you have any questions, in the meantime I'll sort out the remaining points.

BBF