Alternate row colour after 7 rows/after each new group

[sivarammoto]

Hi All,

I have a requirement of showing alternate row colour in a table after every 7 rows. Below is an example . For the first 7 rows i would like to have background color is grey and the next 7 rows it is blue and again for next 7 rows it is grey so on... I have explored conditional formating but am not able to get in to there. Request your expert views in solving this.

S. No	Type	Qty
1	Opening	10
1	Received	5
1	AAA	0
1	BBB	0
1	CCC	0
1	Sold	5
1	Closing	10
2	Opening	15
2	Received	5
2	AAA	0
2	BBB	0
2	CCC	0
2	Sold	5
2	Closing	15
3	Opening	10
3	Received	5
3	AAA	0
3	BBB	0
3	CCC	0
3	Sold	5
3	Closing	10
4	Opening	15
4	Received	5
4	AAA	0
4	BBB	0
4	CCC	0
4	Sold	5
4	Closing	15

[Anonymous]

One way to solve this is with a measure: 

EvenOdd = IF(ISEVEN(SELECTEDVALUE(Table1[S. No])) = TRUE();1;0)

Then use conditional formatting based on that measure, change minimum to number 0 and maximum to number 1 and then choose color. 

Downside is you have to do it for each column seperately.

Hope it helps!

[sivarammoto]

Hi Andy,

Thanks for your quick reply.. 

S.No which i mentioned in my example was dummy data. In real scenario S.No will be like "ABC7089010". So "IsEven" function will not be useful. I have also tried "Mod" function but that is not working.  I am exploring permanent solution where if i add new column also it should work instead of applying conditional format on each columnn seperately

[v-lili6-msft · Accepted solution]

hi, @sivarammoto 

If each [S.No] in visual all have 7 rows, If so, just try this way:

Use RANKX Function to add a rank measure for [S.No], and then use IsEven Function nested it.

EvenOdd = IF(ISEVEN(RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])),,ASC,Dense)) = TRUE(),1,0)

Then use conditional formatting based on that measure, change minimum to number 0 and maximum to number 1 and then choose color. 

you could also add a mapping rank table for [S.No], then use it for rank measure.

If each [S.No] in visual don't all have 7 rows, for example:

[S.No]=1 only has 5 rows, [S.No]=2 has 7 rows, what is the expected output?

1. when you want the output is still the same each [S.No]

Do the same logic as above.

2. when you want the output is alternate row colour after 7 rows, just adjust the formula as below:

Measure = 
IF(ISEVEN(INT((RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])&SELECTEDVALUE('Table'[Type])),,ASC,Dense)-1)/7)) = TRUE(),1,0)

best regards,

Lin

[sivarammoto]

Thanks Lin,

Your solution is working fine. However in this case i have to apply conditional formatting for every column, is there any way that i can create a variable and assign that variable at row level so that even if i add new column also it works .

Regards

Siva