Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
VilleQvist
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.

0 REPLIES 0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors