OK, I think I’ve got this working in a way that meets your requirements.
The first thing I did was rework your data model into a standard star schema by combining the Zoho Accounts and Zoho Contacts and removing the resulting duplication, getting rid of the unnecessary bidirectional relationships and the bridge table. I suspect you had the bidirectional relationships set up in order to sync the slicers on those two tables. However, especially given the large amount duplication between those tables, de-normalizing them into one table accomplishes the same thing and allows us to get rid of the undesirable bidirectional relationships. I also had to add a dimension table for Display Month, used in both the slicer and to get the matrix to calculate properly. Here’s the resulting data model following these changes:
Now the measures to calculate the matrix values and to do the conditional formatting of the matrix values become pretty straightforward and allow us to distinguish both computationally and visually between zeros and blanks:
IF( SELECTEDVALUE( 'Zoho Accounts'[Current Month Open] ) < SELECTEDVALUE('All Display Months'[MonthDisplay Numeric] ),
IF( COUNT( 'ISN Orders'[oid] ) < 1,
COUNT( 'ISN Orders'[oid] )
Cond Format Zeros and Blanks Background =
[Inspection3] = 0, "red",
[Inspection3] > 0, "light blue",
Here’s what it looks like all put together:
I think the way you’ve the structured cross-time/cross-franchise comparisons is very clever, and I enjoyed working through logic of it. With the conditional formatting applied, the underperforming franchises jump right off the page.
I hope this is helpful to you. Full solution file attached.