How to get the conditional formatting based on data variance from 2 table columns.

[Anonymous]

I am trying to conditional formating to show up, down & nutral key if the total count of a vaule of the column in go up or down compare to previous month count of vaule of the column. 

As explance shown below. 

Thanks

[Anonymous]

@rubayatyasmin , you assistance help me to achive the 85% of the requirement however I still have one challenge this the substaction. If use this formula 

Variance of fruit count from LW = [Fruit Count TW] -  [Fruit Count LW] then we are getting value in "-". e.g. 6-3 should be 3 not -3 somthing like that...

[rubayatyasmin]

Brother see the logic plz. The measure is subtracting this week - Last week. so 3-6 =-3

Appreciate some kudos.

[Anonymous]

@rubayatyasmin thanks for your extense help and I really appriciate it... Is there a way we can remove the netgative marking from the calculation... Just curious because we have the conditional formating icon to show if the number is up or down.

[rubayatyasmin · Accepted solution]

yes, we can do it. just change the number format to show the negetive value enclosed with brackets. 


As your main query is solved so you should accept that answer as solution and appreciate the kudos. 

[Anonymous]

I am sorry I am still not understand, how to change the number format... I would like to avoid should negative marking in the variance column 

[Anonymous]

@rubayatyasmin thanx your response, however inre ply there is no option to attach a file

[rubayatyasmin]

go to file.io and upload your file. 

[Anonymous]

Hello @rubayatyasmin unfortunatly, I dont have the option of file.io option available and my orginization policy prevent me to upload the file to any personal drive like dropbox or OneDrive

[rubayatyasmin]

That is sad to know. 

See this doc helps you. https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

or this one Solved: Conditional Formatting of the column cell based on... - Microsoft Fabric Community

[rubayatyasmin]

@Anonymous 

can you please provide demo PBIX file?

[Anonymous]

Hello @rubayatyasmin, I manage to create a demo file on my personal device and here is the link for your refrence.  

https://file.io/SSdW7OkRT8Y1 

I looking to achive the below requirement if possible:

My expectation is to calculate the variances in the rows by comparing the total counts of the fruits from this week and last week

If the count each rows of fruits in "This Week" table is biger than last week then a up arrow key show show in a new column and the row\count cell should trun red

If the count each rows of fruits in "This Week" table is less than last week then a down arrow key show show in a new column and the row\count cell should trun green

If the count each rows of fruits in "This Week" table is less than last week then a down nutral/equal key show show in a new column and the row\count cell should turn Light yellow

[rubayatyasmin]

I got your file. I will try this and let you know

[Anonymous]

Thank you so much will wait for your response...

[rubayatyasmin]

HI, @Anonymous 

I have tried to solve your problem and came up with this,

i hope this meets your requirements. I tried to use a the append transformation because two table structure is same. I could have draw a relationship but it would be redundent. Because the relationship would be Many-to-many, and nobody likes to work with Many-to-many relationship. 

find the solution file and do not forget to change the data source. 

https://file.io/hKBoK44rza7F

[Anonymous]

Hello @rubayatyasmin, thank you for your efforts, unfortunatly this doesnt meet the ecpectation. Is it not possible to provide the conditional formating in the same current week table based on calculation from last week because I have diffrence is Fruits counts every week.

[rubayatyasmin]

@Anonymous 

Like this?

[Anonymous]

Yes exactly something like this... however the icon needs some chnages like if this count of this week is higher than it should should red up error key... If the count is lower then last week then it it should show Green down key. and Yellow if no value in this week 

Also, the cell background color should be in This Week Fruit count table column

[rubayatyasmin]

find the solution file here. 

https://file.io/r6wATqbZ8gSa

Appreciate some kudos if I helpd you a little bit 👍

[Anonymous]

The files seems to be deleted

[rubayatyasmin]

Here is the solution file

https://file.io/ZxN7jedYPk4o