Hot to apply Conditional Formatting to Calculation Groups and Calculation items

Hi somnath6309 
Is there a reason you can't use Cell elements and use basic conditional formatting?

Hi,

Thanks for the reply. I had applied the tric in my report. Howver, the same is not producing the desired result. Pls find below the screen shots that shows the "Columns", "Values" of the Matrix. The code of the measure FormatFont is also shown below. The Martix does not use only single meaure, it uses four measures that are listed as calculation items under "CalculationGroup2" column name : "Matrix". 

My Report has three Calculation Groups :

01. Metric ( Group Name) that includes Calculation items ( all these are measures) :

   a. Sales Amount 

   b. Sales Qty

   c. TotalCost

   d. Margin Pct

02. Time Intelligence ( Group Name) that includes calculation Items: 

     a. CY

     b. PY

     c. YOY Pct

    d. YOY

03. Average that includes calculation item : AVG

Now let me put the requirement precisely :

Sales Amount, Sales Quantity and Total Cost cannot be negative for CY ( Current Year ) and PY ( Previous year) and hence those figures are to be shown in BLACK. However, the aforesaid items can be both Positive and Negative for YOY ( Year on Year) and YOY Pct and hence the values should appear Green and Red respectively. 

However, Margin Pct can be both positive and Negative for CY, PY, YOY & YOY Pct.

I tried to use the trick as shown above but the following result is appearing:

Note: in the visual, CY and PY of Sales Amt should be Black and YOY and YOY PCT should be red but the same is not appearing. The conditional formatting rule applied to Cell Elements Font Color is as follows:

SQLBI video tutorials available in Youtube named "Using Field Parameters and Calculation Groups in Conditional Formatting" shows some tricks (attached below) but the same is requirement specific and some changes are to be made therein to make it suitable for my particular requirement. However, I do not have achieved that much of skill till date.

Hence, requesting for help so that the specific purpose can be accomplished.

Regards,

Somnath

Using Field Parameters and Calculation Groups in Conditional Formatting 

Hi, Sam,

Thanks for your reply and support. 

I used the process as shown above. In the below matrix visual I put Sales Amount measure in values area, in row area I put Calendar Year and Months i.e. Calendar Hierarchy. I created Time Calculations  Calculation group having column name TimeIntelligence which has calculation items like CY, PY, YOY, YOY Pct.  Following is the screen shot for reference. The precedence is 4 (highest) 

Now my goal is to show CY and PY Vales of Sales in BLACK (regular color) as the same can not be less than zero. However, YOY and YOY Pct are to shown in Red and Green for Negative and Positive values respectively. I used the following measure :

and used the same to do conditional formatting like below :

and every thing is working fine as desired :

However, I had created anothere calculation Group named Calculation group 2 having column name Metric with calculation items Sales Amount, Sales Qty, Total Cost  and MarginPct as shown below :

all the calculation items are measures :

I created a slicer and put the 'Calculation Group2'[Metirc] to the Field area of the Slicer :

Now the user can choose the items to be displayed in Matrix value area. However, when the "Sales Amount " is selected from the slicer the figures in CY and PY column got Green color. Obviously I put some conditional formatting as shown below screen shot:

the measure named "FormatSales" is as follows:

however, the requirement is still the same as previous: 

when Sales Amt , Sales Qty, Total Cost are selected by the user from the slicer, figures in CY and PY column should have regular black color, however, values in YOY and YOY Pct columns should be displayed in Red ( for Negative value) or Green ( Positive value). The report should look like the following :

Please let me know what needs to be done to get the above result.

Thanks and Regards,

Somnath6309

Hi somnath6309 ,

Thank you for reaching out Microsoft Community.

Create a new measure that returns the color based on the result of SELECTEDMEASURE(),
You can use SELECTEDMEASURE() in a Calculation Group context because this measure will evaluate dynamically across calculation items.

Apply Conditional Formatting in Matrix

Click on the Matrix visual in Power BI.
In the Values area, click the dropdown arrow next to one of your metrics (like Sales Amt).
Choose Conditional Formatting > Font color.
In the dialog: Select "Format by" > "Field value"
Choose the measure you created: FontColor Format

Repeat this for each measure in the "Metric" calculation group if needed.

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

Hi somnath6309 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

Hi somnath6309 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra E