Apply formula's column rules to compare with dynamically added columns

I have a set of data that consist of multiple different types of product's price for each different markets, MarketA belongs to me and I conditionally formatted it to color code it red when a product's price in our market is higher than every other market's product. Using column rules in table tool, this logic is formed

IF toNumber([MarketB])==0 && toNumber([MarketC])==0 && toNumber([MarketD])==0 && toNumber([MarketE])==0 && toNumber([MarketF])==0 && toNumber([MarketG])==0 && toNumber([MarketH])==0 && toNumber([MarketI])==0 && toNumber([MarketJ])==0 then "background-color :lime" elseif toNumber([MarketA (ours)]) > toNumber([MarketB]) && toNumber([MarketA (ours)]) > toNumber([MarketC]) && toNumber([MarketA (ours)]) > toNumber([MarketC]) && toNumber([MarketA (ours)]) > toNumber([MarketD]) && toNumber([MarketA (ours)]) > toNumber([MarketE]) && toNumber([MarketA (ours)]) > toNumber([MarketF]) && toNumber([MarketA (ours)]) > toNumber([MarketG]) && toNumber([MarketA (ours)]) > toNumber([MarketH]) && toNumber([MarketA (ours)]) > toNumber([MarketI]) && toNumber([MarketA (ours)]) > toNumber([MarketJ]) then "background-color:red" else "background-color : lime" endif

Here is what I want to achieve, since using that logic each time a new market (column) is added into the data set, the code have to be formatted again to add in the new market (column) manually, so I was thinking if there is a way to compare our column (MarketA) with unknown or dynamically added columns instead of manually adding new lines of code each time a new market (column) is added in

attached is the sample workflow for this conditional formatting.

sample color workflow.yxmd

Input

Reporting

Output

Accepted answers

shreyanshrathod

Hi @Anzrey ,

Not sure if this the optimized and the best way to achieve desired results, but it works fine!!

Have a look at it and let me know if it helps.

I have tested by adding several columns, deleting existing columns and by changing prices and it works perfect in all scenarios.

Give it a go!!!

Regards,

Shreyansh Rathod

sample color workflow.yxmd

atcodedog05

Hi @Anzrey

Here is how you can do it.

1. whenever we want to apply logic on multiple variable fields we would need a transpose tool. Here in transpose tool i am keeping Product & MarketA as key and all other columns will be converted to row and by selecting dynamic or unknown columns it converts new columns also into rows (i,e new markets also into rows)

2. As i could see there was a distinct formula that you want to color background red when all markets are lesser then market A. So in the formula i am creating a red column checking whether a market is less than market A if yes assign value 1 else 0.

3. In summarize tool i am grouping by Product & MarketA (key columns), taking sum of red, count of red and sum of value.

4. In the formula i am creating column color to check if sum of value is zero or null then green (1st condition), sum of red and count of red is same then MarketA is greater than all markets then red (2nd condition) else green (else condition)