Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Conditional formatting with calculation groups

I have a model with 2 calculation groups: First one to select measured thing (volume, turnover, net sales, count of items is 70+) and second to select "formula" (PY, YTD, budget, rolling 12 months, diff PY, count of items is 61). I have also unit selection table (thousands, millions, per sales volume unit etc, 6 items) what I use to choose correct format string or calculation (eg. user want to see net sales / sales volume not the net sales itself) with formulas.


I have also month index created to my date table. Index 0 newest closed month, 1 is last month and -1 is next month etc. So my plan is use that month index to create conditional format rule like if index >= 0 color is red, if index < 0 color is green. But there are couple of problems


1) Max(day[monthIndex]) gives wrong index when using PY/similar "formulas". This I can hande by using if(selectedvalue(formulaCalcGroup[formula]) in {"PY", "YTD PY"...} index - 12, index) and similar calculations.

2) But this does not work with second calculation group. I think that the problem is that calc group replaces the measure.

3) In some cases I can make a workaround: Replacing dynamic measure with static ones (like taking sum of net sales and does not use the calculation group to select the measured thing) but it does not work with unit table IF i modify the calculation (transform it to per unit). It works fine it I just change the format string (means thousands and millions).


In normal case, everything works well. It might be a little bit slow if you have selected a lot of measured things and a lot of formulas but other than that everything works well. But the conditional formatting does not work at all.


Do we have any solutions, or do I just need to wait that Microsoft implements better conditional formatting with calculation groups?


Extra infos:

My "Dynamic measure" calc group is very simple. All the items are just links to corresponding measures eg. item "Sales volume": It is just [Sales volume]


Formulas calc group is the thought one: There are all the calculations and even "act" is like if(selectedvalue(unit table[unit]) <> per sales volume, selectedmeasure(), divide(selectedmeasure, sales volume])), and all other items are more complicated (most complicated formulas are more than 100 rows of code).


Unit table is used to change format strings eg. : if(selectedvalue(unit table[unit]) = "Thousands", "#,##,", "#,0")


My normal matrix is something like this:

- Rows: Products or Customers

- Columns: some compination with Month, Dynamic measure, formula

- Values: Any measure is ok, I have "bmeasure", which is only blank(). Calculation groups replaces its anyway so you does not need to create anyhing in the measure itself.


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors